Code to prompt "password" to view sheets

Here's some code I've used in the past to hide a sheet called 'Compensation', and returned to a sheet called 'Input' when 'Compensation' was hidden:

Sub ToggleCompSheets()

Dim ConfirmHide As String

Application.ScreenUpdating = False
If Sheets("Compensation").Visible = xlSheetVeryHidden Then
GetCompApproval
Sheets("Compensation").Visible = xlSheetVisible
Sheets("compensation").Activate
Else
ConfirmHide = MsgBox(prompt:="This will hide the Compensation tab." & vbCr & vbCr & _
"You will need a password to access " & vbCr & _
"the tab if you choose to continue." & vbCr & vbCr & _
"Do you wish to continue?", Buttons:=vbYesNo, _
Title:="Hide Compensation Tab")
If ConfirmHide = vbNo Then
End
Else
Sheets("Compensation").Visible = xlSheetVeryHidden
Sheets("Input").Activate
'Range("c8").Select
End If
End If
End Sub

Sub GetCompApproval()
Dim Password As String
Dim RetryPass As String
Password = InputBox(prompt:="Enter password to display Compensation tab", Title:="Password")
'MsgBox prompt:=Password
If Password = "" Then
End
ElseIf Password <> "1966" Then
RetryPass = MsgBox(prompt:="Sorry, password is incorrect" & vbCr & _
"contact Mark" & vbCr & _
"if you need a password", _
Title:="oops!", _
Buttons:=vbRetryCancel + vbExclamation)
If RetryPass = vbRetry Then
GetCompApproval
Else

End

End If
End If

End Sub

HTH

-Mark

Hi Rick, here is one way to do it. It would be better if you developed a Login Form instead of using the Input box that you see in my code, that way you could us a text box for the password and change the PasswordChar property so that you cant see the password as it's typed.

This example will hide all the sheets except Sheet1 when you open the workbook, and unhide them when you click the button after verifing the password.

'This code goes in your ThisWorkbook Module
Sub HideShowSheets(sExceptionSheet As String, bHide As Boolean)
Dim iX As Integer

For iX = 1 To ThisWorkbook.Sheets.Count
Sheets(iX).Activate
If ActiveSheet.Name <> sExceptionSheet Then
If bHide = True Then
Application.ActiveSheet.Visible = False
Else
Application.ActiveSheet.Visible = True
End If
End If
Next iX

End Sub

'This code goes in your ThisWorkbook Module
Private Sub Workbook_Open()
Call HideShowSheets("Sheet1", True)
End Sub

'This code goes in Sheet1, change the name to match your command buttons name.
Private Sub cmdShowSheets_Click()
Dim sPassword As String

sPassword = InputBox("Enter Your Password")
If sPassword = "mypassword" Then
Call ThisWorkbook.HideShowSheets("Sheet1", False)
Else
MsgBox "Password incorrect!"
Exit Sub
End If
End Sub

Hope this helps, Jerid