Making my spreadsheets "Hack Proof"

[danelskibr]

Hello,

I have a spreadsheet that is shared with people throughout the company, but what they can see and do with the spreadsheet is limited

I have password protection on the VBA Project, the workbook, and all the sheets.

I allow the users to interact with the spreadsheet through macros. When a user clicks a button to run a macro, the macro will unprotect certain portions of the workbook in order to run without errors. At the end of the macro, code runs to protect everything once again.

The problem is that if I run the macro and then hit the "Esc" key repeatedly, I will break the macro in the middle of it running and after it has unprotected certain parts of the workbook. This allows the user a way into the protected aspects of the sheets.

Is there a way to prevent this type of breach?

Thank you for your help!

 

[V_Malkoti]

You can use UserInterfaceOnly parameter of Sheets.Protect method to specify that password protection applies only to UI and not to macro. With that, you macro will no longer need to first call Unprotect method to work on the sheet. So even if the macro crashes, the sheet UI is still protected.

Sheets("Sheet1").Protect Password:="abcd", UserInterfaceOnly:=True

 

[danelskibr]

Thank you for that! This is a much better solution than calling unprotect macros.

Can the same logic be applied to unprotecting the workbook?

Thanks again!

 

[RoryA]

First, I should point out that you can't make a workbook hack proof.

There is no equivalent to the UserInterfaceonly parameter for workbook protection (and you should be aware that UIO is not 100% reliable in allowing worksheet operations) but you can use the Application.EnableCancelKey property to disable the Esc key while your code is running.

 

[danelskibr]

I figured I might get a little flack for that title! That is why I put hack-proof in quotes. Most people don't know how to unhide columns in my world, so password protected worksheets are pretty much impenetrable.

Thanks for the response. I will give that a shot!