Excel workbook protection: how can I stop users tampering with a workbook?

Question

I have a workbook with a lot of sheets, macro's and calculations going on. I now need to send this sheet out (not forseen before yesterday). How do I lock it down so that users:

• are unable to access/see the macros?
• are unable to unhide hidden sheets?
• are unable to see code or add code/

I've gone through and unlocked those cells I want them to change. Hidden rows of buttons and formulas and then locked them. Hidden worksheets. I've now protected the worksheet with a password.

However, does this stop users from accessing the Macro's/Code? or unhiding sheets? If not, how can do I lock the workbook down correctly/securely/totally?

It sounds like a big-brother type of security protection, but I'm just making sure that all the interlinked cells are not accidently changed, or a macro is run that I don't need them to run.

Many thanks

Michael.

Answer 1

First - note that most of the protection features in Excel are not security features. Most are quite easily broken or bypassed. They are fine to stop accidental damage - but will not stop anyone who knows what they are doing and really wants to see bits you've hidden.

That said, here's some answers:

Inital Protection

You've covered this, basically lock cells and hide things and needed.
Then protect every sheet.
Then protect the workbook itself.

Very Hidden Sheets

Sheets can to be made "very" hidden, this means then an only be unhidden via VBA (they don't appear in the unhide list in the interface). This is simply done via a line of VBA, which needs to be run at least once to trigger the change of setting, and doesn't need to be kept in the file once run (but this might be useful to remember which sheets to unhide later):

Sheets("Sheet1").Visible = xlVeryHidden

Hidden VBA Code

Set a password on your project. From the VBA editor right-click your module in the Project Explorer, choose <ProjectName> Properties and set a password on the Protection tab.

Note that these can be brute-forced open.

Hidden Macros

I assume here you mean you don't want them to appear in the macros list when someone opens the Run Macro window?

To do this make the desired macro either a Private Sub or a Function, this makes them non-visible as macros but still allows them to be called by other code in the same module.

I don't think you can't prevent people from running the visible macros and you cannot assign hidden macros to buttons.

Answer 2

If you only want recipients not to change some bits accidentally, Excel's protection will suffice. But if there is confidential data that you don't want people receiving the workbook to be able to see, they're no good.

If you only want to show the data and not the formatting, a simple way of “flushing” all hidden data is to export each sheet as CSV, then reimport them as a new Excel workbook. (The second step is strictly speaking optional, but people are not used to CSV files, so it's better to send an Excel file like they expect.)

If you want to have presentable output that people can't modify, print the sheets to a PDF file and send that.