Protecting a Hidden Sheet

y2k · Mar 1st, 2002, 03:53 AM

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 · Mar 1st, 2002, 04:31 AM

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 · Mar 1st, 2002, 05:24 AM

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 · Mar 1st, 2002, 05:43 AM

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 · Mar 1st, 2002, 06:20 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 1st, 2002, 03:53 AM	#1
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	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???????

Mar 1st, 2002, 04:31 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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

Mar 1st, 2002, 05:24 AM	#3
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	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.

Mar 1st, 2002, 05:43 AM	#4
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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.

Mar 1st, 2002, 06:20 AM	#5
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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 ]