VBA: unhide sheets with password

[manny88]

I have a basic 2 button userform and 4 worksheets.

Button 1 is for all users
Button 2 is for admin only

When selecting button 2 I need all hidden worksheets to become visible, which they are doing... but i need this to happen with a password prompt. If it is incorrect, try again or cancel (the user can then manually exit or use button 1)

I have used the following:

With sheets("Admin")
With sheets("Cases")
With sheets ("Other")
.visible = IIf(InputBox("Enter Password") = "Password", xlsheetvisible, xlsheetveryhidden)
If .visible = xlsheetveryhidden Then msgbox "Incorrect Password. Try again."
End with
End with
End with

Right now, the password prompt works, but even if you enter an incorrect password it unhides all 3 sheets. Also, if you click Cancel that also unhides all 3 sheets.

Please help!

 

[FuNeS13]

you might want to check your spelling on your if... I believe there are two I's there...

 

[Trevor G]

Question:
What happens if someone changes a sheet name, I believe your code wouldn't work in that case, perhaps you could use the real sheet name. In the VBA window you will see each sheet name but it will also indicate the real sheet name an example is Sheet1 and the user side of Excel would display the tab name "Cases".
You have stated your using a Basic Userform then you could add a textbox and refer to the textbox rather than an inputbox.
Have you created your access list on a sheet = "Admin" and have you got each users name and the assigned password they have in a table to limit the access to what sheets they need to see.

I am happy to help but need these questions answered to provide you a solution.

 

[manny88]

Trevor,

Thats a good point. I have amended the code to reflect the real sheet name.

I figured the inputbox would be less hassle, but it's proving otherwise.

Users get full access to the document if they enter the correct password. My intention was for the password to unhide and unprotect all worksheets in the workbook - 2 birds 1 stone, if you will.

Is that possible?

 

[Trevor G]

I have created 3 sheets only in a workbook using your names and the code below is based around them.
I have created a userform with a command button and a textbox. Object names are as follows:

Userform = frmPassword
Command button = cmdOK
Textbox = txtPassword

Behind the WorkBook Event for On Open I have added this code:

Private Sub Workbook_Open()
'Hide two sheets to veryHidden
Sheets(1).Visible = xlSheetVeryHidden
Sheets(2).Visible = xlSheetVeryHidden
'Need One sheet visible so protected this sheet
Sheets(3).Protect
'Display the userform
frmPassword.Show
End Sub

Code for the Command button

Private Sub cmdOK_Click()
'Declare Password as string
Dim strPassword As String
'What is the password
strPassword = "Password" 'This is in the correct case
'User types in the correct password
'Make all sheets visible and unprotect
If Me.txtPassword = strPassword Then
Sheets(1).Visible = True
Sheets(1).Unprotect
Sheets(2).Visible = True
Sheets(2).Unprotect
    Sheets(3).Unprotect
'Close the UserForm
Unload Me
Else
'Incorrect Password display message
MsgBox "Incorrect password please try again", vbInformation, "Password"
'Reset Textbox to let them try again
Me.txtPassword = ""
End If
End Sub

Just as another quick point under the Review Tab there is an option to protect Workbook so no code needed.