Password Protect Sheets Help

[Shelly90]

Helloo,

I have found some code online to prompt for a password when selecting a single tab. However when I edit it to do this on several tabs it prompts for the password several times over... can anyone help please. I just want to select a sheet and it prompt for a password, instead it seems to be running the code over and over a few times. When I add more sheets to the code it increases the amount of times it wants the password..

#Confused .

Sorry I'm not that great at VBA.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheets As String, Response As String
MySheet = "Sheet3"
If ActiveSheet.Name = MySheet Then
ActiveSheet.Visible = False
Response = InputBox("Enter password to view sheet")
If Response = "MyPass3" Then
Sheets(MySheet).Visible = True
Application.EnableEvents = False
Sheets(MySheet).Select
Application.EnableEvents = True
End If
End If
Sheets(MySheet).Visible = True
Dim MySheets1 As String, Response1 As String
MySheet1 = "Sheet2"
If ActiveSheet.Name = MySheet1 Then
ActiveSheet.Visible = False
Response1 = InputBox("Enter password to view sheet")
If Response1 = "MyPass2" Then
Sheets(MySheet1).Visible = True
Application.EnableEvents = False
Sheets(MySheet1).Select
Application.EnableEvents = True
End If
End If
Sheets(MySheet1).Visible = True

End Sub

 

[Jeffrey Mahoney]

Shelly,

Please try this. Can you please make sure to post your code within the CODE brackets. Its' much easier to read because of the text being indented.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  
  Dim MySheets As String, Response As String
  
  MySheet = "Sheet3"
  If ActiveSheet.Name = MySheet Then
    Application.EnableEvents = False
    ActiveSheet.Visible = False
    Response = InputBox("Enter password to view sheet")
    If Response = "MyPass3" Then
      Sheets(MySheet).Visible = True
      Sheets(MySheet).Select
    End If
    Application.EnableEvents = True
  End If


End Sub

 

[mumps]

What is the purpose of the password? Is it to make the sheet visible? If this is the case, you don't need a password because it is already visible to allow you to select it. Is the purpose of the password to unprotect it? Please clarify.

 

[Shelly90]

Shelly,

Please try this. Can you please make sure to post your code within the CODE brackets. Its' much easier to read because of the text being indented.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  
  Dim MySheets As String, Response As String
  
  MySheet = "Sheet3"
  If ActiveSheet.Name = MySheet Then
    Application.EnableEvents = False
    ActiveSheet.Visible = False
    Response = InputBox("Enter password to view sheet")
    If Response = "MyPass3" Then
      Sheets(MySheet).Visible = True
      Sheets(MySheet).Select
    End If
    Application.EnableEvents = True
  End If


End Sub

This is great. When I get the password wrong though, it hides the worksheet. and you have to unhide again for it to prompt for a password again.

Basically what I'm doing is I have a team of people, each has a TAB.
I have a list of their names which hyperlinks to their tab. When I click the hyperlink it prompts for their password now thanks to your code. However if they get it wrong it hides the sheet and if I click on their name (hyperlink) again it doesn't do anything because the sheet has now been hidden.

 

[Jeffrey Mahoney]

Yeah, I thought hiding the sheet was a little clumsy. I think you should put a button on a main sheet to ask for a password to unhide the sheet. Keep the tab hidden until the enter the right password.