Copy Excel Sheet with VBA Loses Protection

Question

Is there a more secure way of protecting excel sheets without embedding the password in the vba code with sheet.Protect code? Required functionality is to copy excel sheets with a macro and retain protection so formulas remain hidden.

Two template sheets have hidden formulas. Copy protection on these sheets allow users to do everything except Edit objects and Edit scenarios.

The sheets need grouping functionality which requires VBA code (added to workbook_Open) to set sheet.Protect UserInterfaceOnly:=True for any sheets with ProtectedContents = True, and .EnableOutlining:=True. All other Protect attributes are set to True in the code except DrawingObjects and Scenarios.

Copying either protected sheet with the excel interface maintains protection on the copy. Copying a sheet in VBA code results in a new sheet with no protection.

My concern is despite protecting the code with a secure password in excel 2010 the code and the embedded password is easily displayed by opening the file in other software. Is there a more secure way of protecting the sheets without embedding the password in the code?

Reference: Retaining Existing Excel Worksheet Protection When Enabling Grouping

Chris Melville Chris Melville · Accepted Answer · 2018-04-08T11:54:13

By nature, Excel security is not particularly strong. The protection functions exists mainly to prevent lay users from making inadvertent mistakes. However any determined and capable person won't take too long to hack/crack any security you put in place. As an example of how easy it is, take a look at one of the most up-voted questions here:

Is there a way to crack the password on an Excel VBA Project?

Therefore even if you protect the actual VBA project, it's relatively simple to get around this.

In your specific case, you're using a Worksheet_Open event to apply protection - however this is the easiest thing in the world for anyone to bypass, by simply opening the workbook in question via a sub in another workbook, after having turned events off. You may also have some users whose macro security settings are not automatically enabled, and if they dismiss the "Enable Macros?" prompt when they open your workbook, the Open event will never fire.

If you're happy with this level of security - and happy that your workbook won't be the target of hacks from determined users - then you needn't worry too much about storing the protection password in the code itself. However there are a couple of simple ways you can at least make it less visible (in case a user accidentally presses Alt+F11, or an error causes the VBE to open:

Instead of typing the password in the code in plain text, use the Chr() function. So instead of:

Dim pw As String
pw = "hello!"

... you could write:

Dim pw As String
pw = Chr(104) & Chr(101) & Chr(108) & Chr(108) & Chr(111) & Chr(33)

Another way is to store the password within a cell (for example A1) of another sheet whose visibility is set to xlVeryHidden, and reference Sheets("HiddenSheetName").Cells(1,1).Value.

As I said - these are not perfect ways of securing sensitive data and formulæ: however if your goal is simply to stop casual users from making a mistake, or poking around where they shouldn't, then they will do the job.

Additional: for multiple instances requiring the password, I use something like this myself:

Function pw() As String
    pw = Chr(104) & Chr(101) & Chr(108) & Chr(108) & Chr(111) & Chr(33)
End Function

Then call it as needed. You can even name the function something else slightly cryptic, not "pw" etc.

Copy Excel Sheet with VBA Loses Protection

1 Answers