VB Script to hide/unhide worksheets

[derek.hughes]

I made a spreadsheet with employee leave balances. There is a "HomePage" worksheet with instructions, and each supervisor also has their own worksheet with their direct reports listed.

I added a VB script to each worksheet to password protect them and gave each supervisor a password. This prevents them from seeing each others' employees leave balances.

The problem is, if a person clicks and holds on the worksheet tab they can see everything contained on the sheet before releasing and receiving the password prompt - not good.

I would like to rework the spreadsheet so all the data sheets are hidden except for the "HomePage" worksheet, and I would like that "HomePage" to contain macro buttons with each supervisor's name. When the button is clicked I want a prompt to appear requesting a unique password, and if the correct password is entered I want the appropriate supervisor's sheet to unhide. If the incorrect password is entered I want it to error back to the "HomePage" worksheet. Do you know a way to do this?

Here is the current script I am working with, and thank you for your time.

Private Sub Worksheet_Activate()
ActiveWindow.WindowState = xlMinimized
If InputBox("Enter Password for this sheet") <> "HRadmin" Then Sheets("HomePage").Activate
ActiveWindow.WindowState = xlMaximized
End Sub

 

[lrobbo314]

This is on a small scale, the Home Page and 4 other sheets, but the idea seems to work well.

Private Sub Workbook_Open()
Dim x As Integer
Dim ws As Worksheet
x = 2
    For x = x To Worksheets.Count
        ActiveWorkbook.Sheets(x).Visible = False
    Next x
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim x As Integer
Dim ws As Worksheet
x = 2
    For x = x To Worksheets.Count
        ActiveWorkbook.Sheets(x).Visible = False
    Next x
End Sub

Private Sub CommandButton1_Click()
Dim pWord As String
pWord = InputBox("Enter Password", "Security")

    Select Case pWord
    
        Case "password"
            Sheets(2).Visible = True
            Sheets(2).Select
            
        Case "abc123"
            Sheets(3).Visible = True
            Sheets(3).Select
            
        Case "777"
            Sheets(4).Visible = True
            Sheets(4).Select
            
    End Select
    
End Sub

The passwords are in the quotations and correspond to the sheet that should be unlocked.

 

[lrobbo314]

By the way, this is different from what you stated in your original post. With this, you wouldn't have a button for each manager, but rather one button. That button will prompt the user for a password.

If the password is correct it will take them to their page.

Also, below is the same code except it has an error message if the password is incorrect.

Private Sub CommandButton1_Click()
Dim pWord As String
pWord = InputBox("Enter Password", "Security")

    Select Case pWord
    
        Case "password"
            Sheets(2).Visible = True
            Sheets(2).Select
            
        Case "abc123"
            Sheets(3).Visible = True
            Sheets(3).Select
            
        Case "777"
            Sheets(4).Visible = True
            Sheets(4).Select
            
        Case Else
            MsgBox "Incorrect Password!", vbCritical, "Error"
            
    End Select
    
End Sub

 

[lrobbo314]

Also, if you are going to have a workbook set up in this way, you'll need to protect the workbook from being viewed by the users, otherwise all they'll have to do is hit alt+F11 and be able to see everyone's passwords. (Not Good).

In the VBE, go to Tools --> VBA Project Properties --> Protection, click lock project for viewing and set a password.

 

[derek.hughes]

lrobbo314 - THANK YOU! This is great! I get how the code works and I really like the one button approach code.

Here is my issue - mind you, I am new to VB script. I created a command button, assigned a macro, and then pasted the script you listed in the box (editing for appropriate sheet names). However, when I save the macro and go back to the worksheet and click the button - the macro has disappeared.

What am I doing wrong? I get the concept, just don't know the steps to make it work. Could you attach an example?

I REALLY appreciate it.

 

[lrobbo314]

Here's the only thing I can think of at the moment.

1. Open the spreadsheet.
2. Hit Alt+F111
3. Click Insert, Module
4. Then paste the open worksheet and close worksheet parts of the code.
5. Go back to the worksheet (Alt+Q)
6. Go to design Mode
7. Add a command button
8. Right click the button
9. Click view code
10. Paste the rest of the code
11. Save

Hopefully that will be sufficient. Let me know.

 

[derek.hughes]

Worked like a charm! I found my error - I was pasting everything correctly only I was changing the text "Sheet#" to the name I had given the sheet before realizing Excel still thinks of it in terms of "Sheet#".

Now I will add a macro to rehide the sheet on exit and it's good.

Thank you again!!

 

[lrobbo314]

Glad it worked for you!