Unprotect sheet with password, without exposing password in the macro

vba excel excel-2007
Lock picture Lock · Aug 16, 2012 · Viewed 47k times · Source

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?

Answer

chris neilsen picture chris neilsen · Aug 16, 2012

Two options:

  1. Password protect the VBA. (While VBA protection is far from secure against malicious intent, it is no worse than the security of sheet protection)

  2. If you set the worksheet protection using VBA you can specify UserInterfaceOnly:=True.

    sh.Protect Password:="Password", UserInterfaceOnly:=True

    Once 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.

Related questions

How to do a "Save As" in vba code, saving my current Excel workbook with datestamp?

I have an Excel Workbook that on form button click I want to save a copy of the workbook with the filename being the current date. I keep trying the the following ActiveWorkbook.SaveAs ("\\filePath\FormFlow To MSExcel\" & Left(…

Read More
Closing Excel Application using VBA

I have used the following without success. The active workbook closes, indeed, but the excel window remains open. Application.ActiveWindow.Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=False Which is the command that terminates the application? EDIT To say a little …

Read More
Excel Date Conversion from yyyymmdd to mm/dd/yyyy

I have been searching for about an hour on how to do this in Excel. I have an Excel file that was created from an old system and I am pulling information from a SQL Server Database, I will be …

Read More