Protecting a Hidden Sheet

[y2k]

Is there any way to hide a sheet and request a password before the sheet can be un-hidden? I need to do this to Multiple sheets (namely 2001, 2002, 2003) but I don't want the password to be asked 3 times, just once to unhide all the sheets together. I know that this will probably have to be done using VBA and this is not a problem. Please can somebody help???????

 

[Mudface]

If you hide allt he sheets you want to and then protect the workbook with a password, the following will unprotect and unhide all sheets (including charts) you have hidden: -

Public Sub UnprotectandUnhide()

Dim sh As Object
Dim Pass As String

Pass = InputBox("Please enter password to unhide sheets")

On Error GoTo ErrorHandler
ActiveWorkbook.Unprotect Pass

For Each sh In Sheets
If sh.Visible = False Then sh.Visible = True
Next sh

Exit Sub

ErrorHandler:
MsgBox "Wrong password, please re-enter"
On Error GoTo 0

End Sub

 

[y2k]

Tht's exactly what I need. You're a genius!!!!! Thanks. I've got an Auto_Close Macro which hides the sheets if they're visible. The Macro you gave me is just perfect. However I have 2 questions. When I record the Auto_Close Macro, is there any way to specify the password? I don't want the user to be prompted for it. Secondly, not as important. When I type the password at the prompt (the one from your macro) the password appears. It's not protected from others viewing it. Can we change it so that it's protected and just appears as *'s???? I'm probably explaining myself very badly. Thanks again.

 

[Mudface]

For the Auto_Close macro, you could just use: -

ActiveWorkbook.Protect Password := "YourPassword"

As for the asterisks, I believe you can do this with a UserForm, but not with a normal InputBox. Maybe Juan Pablo or one of the other experts could help you out with that.

 

[Mudface]

Just realised, you could use a UserForm with a command button and a text box. Right-click on the textbox and select properties. Change the PasswordChr from blank to an asterisk. Double-click on the command button and enter the following in it's click-event: -

Call UnprotectandUnhide

Then amend the sub above to the one below: -

Public Sub UnprotectandUnhide()

Dim sh As Object

On Error GoTo ErrorHandler
ActiveWorkbook.Unprotect UserForm1.TextBox1.Text

For Each sh In Sheets
If sh.Visible = False Then sh.Visible = True
Next sh

Unload UserForm1
Exit Sub

ErrorHandler:
MsgBox "Wrong password, please re-enter"
On Error GoTo 0
Unload UserForm1

End Sub

You'll have to work out some way of showing the UserForm, maybe a command button on the sheet would be best.

HTH.
This message was edited by Mudface on 2002-03-01 05:20