Question posted 2012 · +7 upvotes
I have recently started writing some Macro’s in Excel. I have a protected worksheet, and a few buttons that allow the user to add/remove columns/rows at a certain point in the spreadsheet.
I currently unprotect the worksheet, perform the functions and then protect the worksheet.
The problem is, is that the protected sheet password is in plain text in the macro for any curious eyes to see.
How can I have a password on the sheet to protect it, but at the same time, allow my Macro to unprotect the spreadsheet whilst it performs its functions, and then reprotect it, without typing the plaintext password in the macro?
Accepted answer +11 upvotes
Two options:
- Password protect the VBA. (While VBA protection is far from secure against malicious intent, it is no worse than the security of sheet protection)
If you set the worksheet protection using VBA you can specify
UserInterfaceOnly:=True.sh.Protect Password:="Password", UserInterfaceOnly:=TrueOnce set in this way VBA code can modify the sheet without supplying a password. Since the password must be supplied once to apply protection in the first place, run this code from a seperate workbook or addin you keep to yourself.
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.