Hiding sheets with a password?

Topic

New Reply

Using VBA for Excel, Excel 97, I’m trying to HIDE each sheet in a workbook with a password.

It looks like Worksheets.hide doesn’t support passwords, though worksheet.protect does. Any suggestions? Am I approaching this from the entirely wrong direction?

TIA

Reply | Quote

Replies

Do you mean you’re trying to use a password to unhide the sheet, or do you want to both password protect and hide the sheet. If the latter, protect the sheet first. You’ll still be able to hide it.

Reply | Quote

Check out the Visible Property for a worksheet. Setting this to xlVeryHidden in a VBA statement prevents the user from unhiding it. It can only be unhidden through another VBA statement. All you have to do is figure out a good strategy of where to place those statements.

Reply | Quote

A couple of comments:

First, you have to leave at least one sheet visible in the workbook. If you are hiding sheets manually the option to hide the last visible sheet is ‘grayed out’ (IIRC) but if you are doing it by looping though the “worksheets” collection you will get an error (subscript out of range, I think – it happened to me recently)

Second, if hide the worksheet(s) then ‘protect’ the workbook with a password then the user cannot unhide the sheets (the ‘unhide’ option is grayed out).

Third, if you are protecting the workbook through VBA and you don’t want the user to be able to open the module to find the password (the VBA command to protect the workbook is:
ActiveWorkbook.protect password:=Password, Structure:=True, Windows:=False <- note that it contains the password!)

you can do what I had to do recently – place the password in a named range on one of the hidden sheets, then access it by:

Dim Pword as String
Pword = [pwcell].value
ActiveSheet.protect password:=Pword

where pwcell is the named range that contains the password.

So long as the sheet is hidden the password is hidden (and I used xlveryhidden elsewhere in the routine to make sure it was).

Finally – if your workbook contains hidden worksheets you have to know the name to access it, you cannot loop through the Worksheets collection and select them one at a time.

Reply | Quote

“Finally – if your workbook contains hidden worksheets you have to know the name to access it, you cannot loop through the Worksheets collection and select them one at a time.”

Oops – unless things have changed in Excel 2000 (and that is what you are using), I’m afraid that in 97 you can still access very hidden sheets via the Worksheets collection. The only thing very hidden does for you is that it stops users unhiding from menus.

Reply | Quote

“Oops – unless things have changed in Excel 2000 (and that is what you are using), I’m afraid that in 97 you can still access very hidden sheets via the Worksheets collection. The only thing very hidden does for you is that it stops users unhiding from menus.”

Interesting. I am using XL 2000, and when I run:

Sub HideProtect()
Dim Sh As Object
‘ Worksheets(“hidden”).Visible = xlVeryHidden
For Each Sh In Worksheets
Sh.Select
ActiveSheet.Protect password:=”Deaner”
Next Sh
ActiveWorkbook.Protect password:=”Deaner”
End Sub

It operates as expected – the sheets and the workbook become protected. When I remove the comment indicator from the ‘xlveryhidden’ line and run it again with sheets and workbook unprotected I get a “Select method of Worksheet class failed” error message on the “Sh.Select’ line

This is exactly the behaviour when I run the macro as shown (with the xlveryhidden line commented out) but I have previously ‘xlveryhidden’ a sheet through the immediate window. I had assumed that the worksheets collection was affected by including a ‘very hidden’ object.

When I try to run

Sub UnHide()
Dim Sh As Object
For Each Sh In Worksheets
Sh.Visible = True
Next Sh
End Sub

After I have ‘veryhidden’ a worksheet and protected the workbook, I also get an error “Unable to set the Visible Property of the Worksheet Class” at the “sh.visible” line, although this macro works when the book is unprotected.

I think I was confused because I was protecting the w/book, as well as “veryhiding” a sheet within it.

Reply | Quote