VB Script to hide/unhide worksheets

derek.hughes

Board Regular
Joined
Mar 16, 2012
Messages
53
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows
This is on a small scale, the Home Page and 4 other sheets, but the idea seems to work well.

Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 16, 2012
Messages
53

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 16, 2012
Messages
53
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!!
 

Forum statistics

Threads
1,136,303
Messages
5,674,966
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top