Password to view a sheet

[Seanzies]

I have a spreadsheet that has 2 sheets on it. Sheet1 is viewable.
What I want is when you click Sheet2 on the bottom, you need to enter a password in order to view the sheet. I can use VB if needed to make a user form for a password window. But need help with the VB if we use it.

 

[DominicB]

Good evening Seanzies

Something like this wi=ould do what you're asking:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Sheet2" Then
Sheets("Sheet1").Select
a = InputBox("Enter Password:")
End If
If a = "pass" Then
Application.EnableEvents = False
Sheets("Sheet2").Select
Application.EnableEvents = True
End If
End Sub

The only problem is that the password would be viewable as it is typed in. The easiest way around this would be to use a userform instead, as a textbox on a userform has the option of setting a password disguise character, but it shouldn't be too much of a leap to code this into the example above.

HTH

DominicB

 

[Seanzies]

Thanks that worked, and entering in the password is fine.
Is it possible if I hide this sheet2 to have a button connected to this vb.
So if you click on the button (ie. Results) it will automatically bring up the password window to access Sheet2. And if I am not making it too difficult, then when you click out of Sheet2 then it will automatically hide the sheet name from the bottom?

Thanks

 

[DominicB]

Good evening Seanzies

This is still quite possible, but it does complicate matters a little because you're going to have to start hiding sheets and avoiding errors that this brings if you try to select a sheet that is hidden etc, you just need to remember to cover all bases. First off, hide Sheet2 (Format > Sheet > Hide). You will need the event procedure, which has changed slightly:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Sheet2" Then
Sheets("Sheet1").Select
a = InputBox("Enter Password:")
End If
If a = "pass" Then
Application.EnableEvents = False
Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
Application.EnableEvents = True
Else
Sheets("Sheet2").Visible = False
End If
End Sub

and a new macro:

Sub Test()
If Sheets("Sheet2").Visible = True Then
Sheets("Sheet2").Select
Exit Sub
End If
Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
End Sub

Draw a button on Sheet1 using the Forms toolbar, and right click on the button and select assign macro, and assign Test to it. This button will then kick of the event, show the password box but keep the sheet hidden until the correct password is entered.

You will also need to set up a macro on Sheet2 to rehide the sheet and select Sheet1, to return everything to the status quo.

HTH

DominicB