VBA to have password open all sheets or only one

[megera716]

Years ago, someone in this forum wrote me some VBA code to protect a workbook so when you open it, if you enter password "abc123" all sheets are visible and if you enter password "welcome", it opens with only one sheet visible and the other ones can't be unhidden (well they probably can but only if you know VBA and I can assure you, the people at my organization I'm protecting this from do NOT know VBA ?).

I've since left the first organization where I used that code or I would just go copy it but it would REALLY come in handy at my current employer.

Can someone do that for me? The one sheet that should open with "welcome" is named "Case List". I'm using Excel 365.

 

[MARK858]

Probably the link below is to your original post and the code @DanteAmor wrote for you

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

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...

www.mrexcel.com

 

[megera716]

Probably the link below is to your original post

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

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...

www.mrexcel.com

Well looky there, that's the one! I certainly thought it was older than 2019 as well!

OK, just wondering, if you know -- this workbook that I'm trying to protect now has like 17 sheets vs the old one having 4. Do I need to name all 17 sheets like I did with the 4 in the original code? Or there is some sort of "all sheets" shortcut for the Visible and VeryHidden parts of the code? Clearly I don't know VBA either ? but I do love it!

 

[MARK858]

You can loop through the worksheets, for example for the unhide part

Private Sub Workbook_Open()
    Dim pass As Variant, ws As Worksheet
    pass = InputBox("Please enter password", "START")
 
    If pass = "" Then
        Exit Sub
    End If
 
    Select Case pass
    Case "Masterpass"
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next
    Case "Limitedpass"
        Sheets("Accounts").Visible = xlSheetVisible
        Sheets("Finance").Visible = xlSheetVisible
    Case Else
        MsgBox "Invalid Pasword"
        Exit Sub
    End Select
End Sub

 

[Michael M]

Maybe this way, you can add more Case statements to suit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> ("Master") Then ws.Visible = xlSheetVeryHidden
Next ws
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
  Dim pass As Variant, ws As Worksheet

  pass = InputBox("Please enter password", "START")
 
  If pass = "" Then
    Exit Sub
  End If
 
  Select Case pass
    Case "Masterpass"
    For Each ws In Worksheets
        If ws.Name <> ("Master") Then ws.Visible = xlSheetVisible
    Next ws
    Case "Limitedpass"
      Sheets("Accounts").Visible = xlSheetVisible
      Sheets("Finance").Visible = xlSheetVisible
    Case Else
      MsgBox "Invalid Pasword"
      Exit Sub
  End Select
End Sub

 

[megera716]

Maybe this way, you can add more Case statements to suit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> ("Master") Then ws.Visible = xlSheetVeryHidden
Next ws
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
  Dim pass As Variant, ws As Worksheet

  pass = InputBox("Please enter password", "START")
 
  If pass = "" Then
    Exit Sub
  End If
 
  Select Case pass
    Case "Masterpass"
    For Each ws In Worksheets
        If ws.Name <> ("Master") Then ws.Visible = xlSheetVisible
    Next ws
    Case "Limitedpass"
      Sheets("Accounts").Visible = xlSheetVisible
      Sheets("Finance").Visible = xlSheetVisible
    Case Else
      MsgBox "Invalid Pasword"
      Exit Sub
  End Select
End Sub

I think that this one actually works as intended for the most part!

When I open the file in OneDrive (so, in the browser), it does not prompt me for a password and only the Case List sheet is visible (I replaced "Master" in your original code with "Case List").

When I open in Desktop app, it then prompts for password and if I enter the master password, all sheets are visible, and if I enter the limited password, only the Case List is visible. HURRAH.

But is there a way to enter the password while I'm viewing the workbook in the browser to view the rest of the sheets without opening in desktop app?

 

[Michael M]

@megera716
Sorry, can't help you with that one...hopefully someone elkse can jump in and assist