Do you have worksheets in your workbook that contain sensitive data? Do these workbooks contain other data that you need to be available to all? Are you looking for a way to keep your sensitive data under wraps? Well look no further. I have the answer.
If a user knows how, they can see that worksheet. If they don’t, you have a couple of options. First, you can hide the sheet through the user interface. Choose Format > Sheet > Hide and voilà, your sheet is gone. You have to keep at least one sheet visible, so don’t try this on your single sheet workbooks. To unhide that sheet, Format > Sheet > Unhide will display a dialog box of all the hidden sheets. As far as I know, there’s no way to prevent a user from unhiding a sheet that appears in that dialog. That is, there’s no password scheme or way of protecting the sheet or way of protecting the workbook that prevents the unhiding of a sheet. Unfortunately, even your two-index-finger-typing boss knows, or can figure out, this method.
If you want to take the next step, you’ll need to learn a little VBA. Don’t be frightened, I’ll hold your hand all the way through. There is a way to hide a sheet, using VBA, so that it doesn’t appear in the Unhide dialog box. You can implement this method without putting macros in your workbook too.
To accomplish this, you’ll open the Visual Basic Editor (VBE), select your workbook, and enter some commands into the Immediate Window. To see the sheet, you’ll do the same thing except the commands will be different. Here are the steps:
Alt+F11 to open the VBE
Cntl+R to view and set focus on the Project Explorer Window
Click the project that says VBAProject(YouWorkbookNameHere) to make it “ThisWorkbook”
Cntl+G to view and set focus on the Immediate Window< Type
ThisWorkbook.Sheets("MySheetName").Visible = xlSheetVeryHidden and press Enter.
Alt+F4 to close the VBE
This picture shows that the VBA Project for Book1 is selected in the Project Explorer window and an appropriate command was entered into the Immediate Window.
You should be back in Excel and the sheet should be hidden. Navigating to Format > Sheet > Unhide should display a dialog box that does not show your sheet. Note that if the sheet you just hid is the only hidden sheet, then that menu command will be disabled as if there are no hidden sheets.
The downside to this is that if the user knows how to do what you just did, they can do the same thing to unhide it. The only difference is what you type in the Immediate Window.
ThisWorkbook.Sheets("MySheetName").Visible = xlSheetVisible and press Enter.
If the person from whom your trying to hide the sheet know his way around the VBE, there’s not much you can do about. Further, if that person reads this blog, this post is probably of little use to you. Sorry for letting the cat out of the bag.