1
I have a workbook (ProtectedWorkbook.xlsx) that has a user interface page (UI Sheet), so when they enter a name, it displays some information on that person. It pulls this information from a hidden sheet (Data Sheet). Data Sheet's Visible property is set to "2 - xlSheetVeryHidden" and password protected. Additionally, the formula in UI Sheet that pulls in data from Data Sheet is hidden.
The problem is that even with all of this protection, a user can still click View Code on UI Sheet, see the name of Data Sheet, and then reference this from another (unprotected) workbook to extract all of the data from Data Sheet.
Is there anyway to prevent this?
Note: I cannot encrypt the workbook or make it read only because the user must be able to enter a person name in the one unlocked cell in UI Sheet.
user7758051
Posted 2019-05-01T22:36:09.300
Reputation: 55
maybe redo/add code to put the value in the cell instead of a formula? – gns100 – 2019-05-01T23:54:03.487
2What if you password protect the VBA code? Then they shouldn't be able to expand the list of sheets from the editor, thus they can't see the name of the hidden sheet. – Christofer Weber – 2019-05-02T00:01:08.907
@ChristoferWeber Even with password protecting the VBA code, you can still view the Microsoft Excel Objects in the VBAProject, so you can see the name of the hidden worksheet. This means that you can then open up a new excel workbook and then reference the cells in that hidden worksheet to extract the data from that sheet. – user7758051 – 2019-05-02T14:18:14.437
1Ok. Well I've never tried password protecting VBA, but I have this document at work thas is locked. And I can't see the objects. At least not with the VBA editor, maybe with some other tool. And you can still extract if you know the name, but still. – Christofer Weber – 2019-05-02T14:29:13.443
@ChristoferWeber That's a good sign. I still need people to be able to change the content in one cell of UISheet. Is that possible while locking the workbook? – user7758051 – 2019-05-02T14:33:53.750
1@ChristoferWeber Actually password protecting the VBA code does prevent the user from seeing the VBA structure. The key is that you have to have some VBA code in the workbook (and save it as a macro-enabled excel file) to password protect the VBA code. Thank you for the help! – user7758051 – 2019-05-02T14:54:21.977
To add to this, the name of the worksheet is still accessible through the Immediate Window using the ?worksheets().name command. – user7758051 – 2019-05-02T14:59:45.037