Very Hidden Worksheets

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.

You can’t.

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

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. VBE

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.

Type

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.

42 thoughts on “Very Hidden Worksheets

  1. Actually there is a way to prevent the casual user from unhiding a hidden sheet. If you password- protect the workbook’s structure (Tools – Protection – Protection Workbook), then the user can’t unhide a hidden sheet unless he knows the password or knows how to crack the protection.

    Also, you can make a sheet very hidden without using VBA. Activate the sheet to be hidden. Display the Control Toolbox toolbar, and click Properties. In the Properties window, change Visible to 2. It can be unhidden only with the VBA statement Dick posted.

    The only sure-fire way to prevent a user from accessing a particular sheet is to delete it.

  2. Of course, even if the sheet is “very hidden” and the workbook is password protected, one can always see the values in that sheet. Open any empty worksheet, select a reasonable range (say, A1:Z100) and array enter the formula ='[{name of workbook with very hidden sheet}]{name of very hidden sheet}’!A1:Z1000

  3. Type ThisWorkbook.Sheets(“MySheetName”).Visible = xlSheetVeryHidden and press Enter.

    What if the sheet name was something only you would know like ‘@$#_445687_doco’ or even something readable that only you would know?

  4. doco,

    Without fully giving the “answer”, remember that the Sheets and the Worksheets properties are just collections.

    In other words, the name of the sheet is not a secure method either.

  5. Another way to use J-Walk’s approach – the Visible parameter can also be selected for the sheet in the Properties Window of the VBE. No immediate code required.

  6. Well another way to “Seucre” the data it to encrypt the contents of the worksheet….but that as secure as a worksheet/workbook password….10 Secs….ok 30 Sec max…to break

    But this is a generic problem(software and hardware)….not something specific to Excel…..every lock has a key….and then there is always a keymaker….

  7. As far as I know, an xlVeryHidden worksheet is only accessible through the VBE. So password-protecting the VBA project makes it quite a bit harder to enumerate the worksheets collection and find sheet Haarige0hhrenR@sierApparat.

    Not, of course, that VBA project passwords are the last word in security.

  8. Aron (xl-logic.com) has an unanswered challage on his site….”Can you hide data in a workbook”
    or something like that

    I guess the answere is no…not just in worksheets….not anywhere…

  9. We’ve developed a combination of approaches to limit access to sensitive data by ‘casual’ end users.

    1.) through the VBE, set the visible property to 2 as explained in this post
    2.) password protect the VBE project for viewing with a strong password
    3.) in another module, we use a windows API for the network username and password triggerd on a workbook open event and force end users to authenticate to the network prior to using the spreadsheet
    4.) set up the ‘authorized’ spreadsheets in a protected area of the network.

    background: we have a group of 15 to 20 endusers who access data and process it from an external source on through to another destination, and we use excel as the ‘translator’.

    at issue is our ability to withstand scrutiny from a 3rd party that the data has not been compromised and there is a traceable history.

    i’m working on developing an audit trail that hooks into 3 above, but am wondering whether it is better to record the audit trail local to the workbook, or export to a common filelog. does anybody have experience with this sort of thing that would be willing to offer general advice?

  10. As a test to demonstrate just how simple it is to unhide all sheets in a workbook, I created new workbook, protected one of the sheets (just for fun), in the immediate window set activesheet.visible = xlveryhidden, added a password to the project (no code in the file), and saved the workbook as Test.xls.

    Then in a new workbook, added the following code to a sheet:
    Sub ShowSheets()
    Dim wks As Worksheet
    For Each wks In Workbooks(“Test.xls”).Worksheets
    wks.Visible = True
    Next wks
    End Sub

    Sheet protection, veryhidden properties and project passwords just don’t help.

    Once you’ve got a workbook open with veryhidden sheets, it would take, at the most, 20 seconds to unhide every sheet in that workbook if you start from scratch, regardless of protective measures taken within that workbook. Two seconds max if you’ve got a custom button on a toolbar that’s ready to go.

    Kruncher

  11. There’s no foolproof way to do this, but you could encrypt and encode data in very hidden worksheets, then use UDFs to decode and decrypt. Have the UDFs check the Application.EnableEvents status and hidden state of worksheets that should be hidden. Return nothing but errors from the UDFs if anything isn’t as it should be. Still possible to get around this, but more difficult to do so.

  12. I generally only use hidden sheets and the like to protect my users from themselves, like the guy who continually tweaks the data file structure (adding multiple rows for more detailed headers, rearranging columns, changing column headers), then complains that my code doesn’t work. So I save the data workbooks in a sister directory, turn the data workbooks into add-ins so he can’t see them at all in the UI, and protect their VBProjects. This way he can only manipulate the data through the mechanism I’ve built into the project.

  13. Another approach would be to get the data from a formula if the sheet itself couldn’t be unhidden for some reason. Like

    Sub test()
    Dim wks As Worksheet, Cel As Range
    For Each wks In ActiveWorkbook.Worksheets
    If wks.Visible <> xlSheetVisible Then
    For Each Cel In Range(“A1:F100?)
    Cel.Formula = “=” & wks.Name & “!” & Cel.Address
    Next
    End If
    Next wks
    End Sub

    (This macro is deliberately bad … )
    Bottom line, as everyone says, no secrets should be stored in accessible Excel files, protection is made only to prevent accidents.

    Best wishes Harald

  14. Right, html. “not equal to” disappears of course. Last seen inside the If statement.

  15. My favourite is to use an old xl4 macro sheet without any protection.
    These sheets do not show up in the VBE, you can still set them to veryhidden:

    Sheets(“Macro1?).Visible = xlVeryHidden

    You can use these to store data, variables, refer in formulas, whatever.

    Looping through the Worksheets collection they dont show up.

    Of course you can find them if you know how.
    But with no protections applied many users would not think to look for them.

    (I assume these macro sheets are still available in xl2007?)

  16. Andrew_D,

    Looping through the Worksheets collection, the XLM sheets don’t appear, but try looping through the Sheets collection.

    And FWIW, Excel 2007 does accept XLM macro sheets in XLS files. Note that it’ll load XLM files, but it won’t save XLM files. Same for pre-Excel 5/95 XLS files: Excel 2007 will load them but won’t save them. Also FWIW, Excel 2007 no longer supports opening or writing Lotus 1-2-3 or Quattro Pro files, presumably because users aren’t demanding the feature any longer, but it still supports DIF files. Is there any software still in use that primarily uses DIF files?

  17. If I save and close a workbook from VB, I find that the worksheet is hidden when I open the workbook. How can I make sure that it opens unhidden.

    Dave P

  18. Hi Dave,

    Are you sure the worksheet is hidden, or is it just not maximized (and possibly off-screen)? After automating Excel, the first workbook opened may not be maximized. Try going to Window | Arrange, select Tile, click OK. Does that show the worksheet?

    Regards,
    Jake

  19. All your answers leave me wondering if this problem can be solved: I have a worksheet with 5 “hidden” worksheets and one active sheet. I would like to create 3 levels of access. The “user” level can only modify columns A – E in my worksheet; The “mgr” level can modify columns A – E and F – J; The “adm” level can modify columns A – J and access hidden worksheets. Is there a way I can use VB to ask for a password when spreadsheet is opened and impose the above controls depending on the user? Based on above comments, sounds like this is impossible.

  20. From the Tools menu, select Protection, and then Allow Users to Edit Ranges. Each workbook user is allotted a range in the sheet with a unique password. It’s possible to create group and level acces policy (depending on OS). More: kbhowtomaster KB289270. You can also use IRM – seems to make sense but you need proper server software.
    I haven’t tested the first solution, the second one requires extra software (Win Server) or you can use free certificates supplied by MS (not sure if they still supply this sevice). Disadvantage – each user must have the certificate installed on PC.
    When the point is to get reliable data and provide specified rights to users rather than to grant security try something else. I’ve chosen another solution for my own purposes (about 15 users, 30 workbooks), not very smart, but quite safe (well, users are not advanced) – each file is password write protected (files are opened in read only mode – no password required as files are in a separated LAN area for specified users). All workbooks and sheets are password protected, if necessary – some cells are blocked and/or formulas are hidden. Most fields have data validation restrictions. For each workbook only one person has granted password – the one who is responsible for entering the data. Hidden sheets are used to make links to other workbooks (very seldom as the there are sometimes errors by opening ### and you need to open the right workbook to refresh data. Analytical and presentation layers are up to users (they are allowed copy data to new workshets and manipulate them). The only problem is (I’m not a VBA man) using most of techniques for data processing. In ‘strong protected’ files you cannot use queries, direct access to data, macros etc. Or perhaps you can but I don’t know how. The perfect tool for automated access is RDBMerge (thanks again, Ron) – http://www.rondebruin.nl, you only need to handle getting data from them all at once and request for a password. In fact you can access data in read only mode but I don’t know how to avoid displaying the ‘give pass or read-only’ box (I scarcely know VBA). Well, not very sophisticated but working, perhaps suitable for not very demanding users.

  21. I need some help with this one – I have a file with two sheets hidden using the VBA Properties under Display Control Toolbox. Two sheets are set to Visible 2 – SheetVeryHidden. I want to reverse this and am trying to use what dick said: ThisWorkbook.Sheets(“MySheetName”).Visible = xlSheetVisible, but it comes up with a runtime error 1004, unable to set the visible property of the Worksheet Class.

    Does anyone know how I can get around this? Thanks, Tiff

  22. I don’t know if there is a way to solve this, but here’s my problem:
    I have a model I built in excel. This model accepts user inputs through a userform and allows them access to some pages while going through the userform. There are sheets that I have hidden (with the VeryHidden commands in VBA) where I do calculations I do not want the user to see.
    A colleague used the method mentioned above about opening a new workbook and unhiding all the sheets in my model to break in very quickly. I tried protecting the structure of the workbook to prevent this, but am now having trouble running the macros that drive the tool. It seems, from what I have read, that I will have to protect and unprotect the workbook as I move through the model, but couldn’t the user hit “break” and have the same access as before?

    Anyone have any suggestions?

    Thanks

  23. Hi Chris,

    In your workbook open event, add two lines to the top that first .Unprotect it, then immediately after re-protect it but the include UserInterfaceOnly:=True argument. This keeps it protected to the user, however VBA will not be hindered.

    Matt

  24. Matt –

    This is what I added into the Workbook_Open() event:
    ThisWorkbook.Unprotect Password:=”locked”
    ThisWorkbook.Protect Password:=”locked”, userinterfaceonly:=True

    This is what I have already included in the Workbook_Open to protect sheets:

    For Each wks In Worksheets
    wks.Visible = xlSheetVisible
    wks.Unprotect Password:=”SecretStuff”
    Next

    For Each wks In Worksheets
    Select Case wks.Name
    Case Is = “Background”
    Case Is = “Hardware and Software Inputs”
    wks.Visible = xlSheetVeryHidden
    Case Is = “Comms Inputs”
    wks.Visible = xlSheetVeryHidden
    Case Else
    wks.Visible = xlSheetVeryHidden
    wks.Protect Password:=”SecretStuff”, userinterfaceonly:=True
    End Select
    Next

    I was thinking I needed to protect the entire workbook, but workbook.protect does not allow an argument for UserInterfaceOnly. Any ideas?

    Thanks

    Chris

  25. Hi Chris,

    You shouldn’t need to necessarily protect the workbook object like that (unless you’re protecting the Structure:=True). You can also lock the VBAProject so the user can’t manually make it visible. You should know though that none of these are foolproof, and if your user is determined enough they can get at everything. They are good at protecting from accidental changes though.

  26. Matt – yeah, it seems from everything I have read that if someone wants to break it, they will. We have a couple of sheets that have calculations and formulas on them that we don’t want the user to see.

    Thanks for the help

  27. Chris,you don’t have to put calculations sheet in the same workbook. The sheets available to users are your source data, where you can set queries or copy data procedures. The analysis layer shoul be in separate workbook in other part of network or at a stand-alone PC. If users need access to all or part of calculations results, you can publish them as HTML or as another workbook/sheet. You decide what you make available, but in form of pure data. Certainly, in such a case there is no real time access granted to users. You can however set procedures on the separated PC to update data (imported and published) e.g. every 60 minutes, if you need.
    Protecting both worksheets and workbooks proved to be very useful for me. I would never resign of it even if it is inconvenient for setting some procedures or activities. I must admit anyway that server-client solutions seem to be much safer. And it is nearly impossible that one of your colleagues knows how to break it.

  28. thanks. it works for me. i’m getting a mess with Hide/Unhide a WORKSHEET based on User’s Role.
    Nice day

  29. I “broke” my workbook by SheetVeryHidden and couldn’t find a worksheet I needed. Gulp.
    Thanks to this post I was able to undo the damage.

    I am truly grateful for the expertise.

  30. Dear Dick Kusleika,

    Thank you very much for giving this information as while working on active X control I had make hidden one worksheet and I was not able to show the same. But because of your tips I have succeeded to reopen the workbook.

    Thanks

  31. ha ha trauling for vba code and found this thread. very interesting. It is all well and good to be able to unhide hidden sheets but you still need to be able to view the content. I have used the following in the worksheet activate event for the sheets I have hidden and want to secure:

    Sub worksheet_activate()

    if application.username “username” then

    sheets(1).activate
    With me
    .hide
    .cells.Font.ColorIndex = 2

    end if

    it is not fool proof but the confusion on the faces when they use their own sheets unhide routine and it truns text white and rehides the sheet is priceless and worth the fun

    Tim

  32. Thanks Bob,

    I have been developing mulitple userforms and my brain was still thinking of the userforms when wrote my comments. It should have read:

    Me.Visible=xlSheetVeryHidden

    not

    me.hide

    Now I best check that I have not coded my userform to say

    Me.visible = false

    note to self “do not multitask while developing”.
    Last week I could not remember how to unhide my carkeys or make visible my wallet.

  33. I like to tie user level access into the Ribbon Controls. This will limit SOME of how users can bypass some of the code, like protect workbook to stop sheets hiding and unhiding even after a password is entered. If the user doesnt have that level of access to begin with, the button doesnt show up.

  34. I have a excel file on which I’ve tried lot of above but cant unhide or break the password. Can anyone please help? I can email the file over.
    Thanks

  35. Those sheets aren’t hidden, just the tabs are. Try this


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.