MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Code to prompt "password" to view sheets

Posted by Rick M on June 29, 2001 5:33 AM

I would like to hide sheet within the workbook and have a button on sheet1 to "view", when clicked it prompts for a paasword and then allows the user to view the other sheets.
Is this possible?
Thanks in Advance.

Posted by mseyf on June 29, 2001 10:22 AM

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
Sheets("Compensation").Visible = xlSheetVisible
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
Sheets("Compensation").Visible = xlSheetVeryHidden
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
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


End If
End If

End Sub



Posted by Jerid on June 29, 2001 10:36 AM

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
If ActiveSheet.Name <> sExceptionSheet Then
If bHide = True Then
Application.ActiveSheet.Visible = False
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)
MsgBox "Password incorrect!"
Exit Sub
End If
End Sub

Hope this helps, Jerid