Display all or only certain worksheets depending on password entered at file open

[megera716]

I have a workbook that is password protected just using Excel's Protect Workbook function (you must enter the password as soon as you open the file or it just doesn't open/you can't see anything).

The workbook has 4 sheets -- Accounts, Finance, HR and Master.

I would like to make it so when you first open the file, it prompts for a password and if you enter a certain password -- let's say "Masterpass" -- all 4 sheets are visible. And if you enter a different password -- "Limitedpass" -- you can only see the Accounts and Finance sheets. Nothing is visible until you enter one of the two passwords.

When a user closes the workbook, when they re-open it (even if just a minute later), it should prompt for the password all over again and not let them see anything until they enter one.

I've been reading endless Google results and I see a lot about this "veryhidden" function but that you actually have to go into the VBA to make it do that and I don't know -- mine just isn't working. But above is what I want it to do. Help please?

 

[DanteAmor]

Perform the following steps:
1. In your book you must have your 4 sheets, but also an additional sheet, You can put any name, for example "Sheet1", the sheet may be protected if you wish, but it must be visible, since in the book you cannot hide all the sheets.
2. In the events of this workbook, put the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Accounts").Visible = xlSheetVeryHidden
    Sheets("Finance").Visible = xlSheetVeryHidden
    Sheets("HR").Visible = xlSheetVeryHidden
    Sheets("Master").Visible = xlSheetVeryHidden
    ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
  Dim pass As Variant
  pass = InputBox("Please enter password", "START")
 
  If pass = "" Then
    Exit Sub
  End If
 
  Select Case pass
    Case "Masterpass"
      Sheets("Accounts").Visible = xlSheetVisible
      Sheets("Finance").Visible = xlSheetVisible
      Sheets("HR").Visible = xlSheetVisible
      Sheets("Master").Visible = xlSheetVisible
    Case "Limitedpass"
      Sheets("Accounts").Visible = xlSheetVisible
      Sheets("Finance").Visible = xlSheetVisible
    Case Else
      MsgBox "Invalid Pasword"
      Exit Sub
  End Select
End Sub

___________________________________________________
IN THISWORKBOOK
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will openthe Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Save the workbook as a macro-enabled file, close it and then re-open it.
___________________________________________________

It works in the following way:
- When you open the book, the code is automatically executed, a window appears with the message to capture the password.
- Capture one of the 2 password you mentioned, it is case sensitive.
- Press Ok.
- The leaves become visible.

The next part is to close the book, when you close the book, the code is automatically executed and the 4 sheets are hidden again, leaving only sheet1 visible.
___________________________________________________
Check if it is what you need.

 

[megera716]

Dante, this is WONDERFUL!! It works perfectly, thank you so much!

Can I ask one more favor? Is there a way to make it so that somebody who has entered the "Limitedpass" password cannot access the VBA (where they could see the master password)? Or, if I cannot restrict their access to VBA entirely when in my workbook and having only entered the limited password, just make it so they cannot see or edit the code.

 

[DanteAmor]

I forgot to put the part to protect VBA, there were already many instructions in my head to tell you.

Do the following to protect VBA:

Within the VBE go to Tools>VBAProject Properties and then click the Protection page tab and then check "Lock project from viewing" and then enter your password and again to confirm it.
Press ok.

 

[megera716]

Perfect again! You're amazing, thank you

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[BryanC72]

I'm glad to help you. Thanks for the feedback.

Hello Dante,

I was hoping to be able to pick your brain on a similar problem that i'm having. Maybe slightly more complicated.

I have a sheet showing consultants results in a pivot and I'm using 2 slicers for filtering.

I'm trying to achieve a similar password situation but I'd like each consultant to have their own password to the same sheet but when they enter their unique password, they are only able to see their own data and nobody else's.

Is this something that can be done while using slicers or even multiple passwords on 1 sheets by linking the passwords to specific filter conditions???

I would like to hide the other consultants names in the slicer once an individual enters their own password so they can't select anyone else.

I hope I've explained this ok.

Are you able to suggest any solution for this problem please.

Thanks in advance.

Bryan