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.

  • maybe redo/add code to put the value in the cell instead of a formula?
    – gns100
    May 1 '19 at 23:54
  • 2
    What 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. May 2 '19 at 0:01
  • @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. May 2 '19 at 14:18
  • 1
    Ok. 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. May 2 '19 at 14:29
  • 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! May 2 '19 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.