How to password protect and hide all sheets except two worksheets?

Question

I came across the following VBA function within this forum that works for password protecting one sheet, but I would like to password protect all sheets except two sheets:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheets As String, Response As String
MySheet = "Sheet1"
If ActiveSheet.Name = MySheet Then
ActiveSheet.Visible = False
    Response = InputBox("Enter password to view sheet")
        If Response = "MyPass" Then
            Sheets(MySheet).Visible = True
            Application.EnableEvents = False
            Sheets(MySheet).Select
            Application.EnableEvents = True
        End If
End If
Sheets(MySheet).Visible = True
End Sub

I have five sheets in my workbook ("Sheet1","Sheet2","Sheet3","Sheet4","Sheet5") and I would like all sheets to be password protected and hidden except Sheet1 and Sheet2. In other words, Sheet3, Sheet4, and Sheet5 should all be password protected with the above code

I've updated the function to be as follows, but it does not even show any other sheets even after entering the correct password

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheets As String, Response As String
MySheet = "Sheet1"
MySheet2 = "Sheet2"
If ActiveSheet.Name <> MySheet And ActiveSheet.Name <> MySheet2 Then
   ActiveSheet.Visible = False
    Response = InputBox("Enter password to view sheet")
        If Response = "MyPass" Then
            Sheets(MySheet).Visible = True
            Application.EnableEvents = False
            Sheets(MySheet).Select
            Application.EnableEvents = True
        End If
End If
Sheets(MySheet).Visible = True
Sheets(MySheet2).Visible = True

End Sub

Answer 1

You aren't using the gifts that Workbook_SheetActivate is giving you. Sh is the worksheet object that is being activated.

Your variable declaration is henky; you declare Dim MySheets As String but never use it and assign MySheet = "Sheet1" and MySheet2 = "Sheet2" without declaring them.

Some levels of worksheet identification are better handled by the worksheet's codename property which changes much less frequently.

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim response As String

    If IsError(Application.Match(Sh.CodeName, Array("Sheet1", "Sheet2"), 0)) Then
        Sh.Visible = xlSheetHidden

        response = InputBox("Enter password to view sheet")
        If response = "MyPass" Then
            Application.EnableEvents = False
            Sh.Visible = xlSheetVisible
            Sh.Activate
            Application.EnableEvents = True
        End If
    End If

    Sheet1.Visible = True
    Sheet2.Visible = True

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    If IsError(Application.Match(Sh.CodeName, Array("Sheet1", "Sheet2"), 0)) Then
        Sh.Visible = xlSheetHidden
    End If

End Sub

Answer 2

Another option is to tie to Named range. You create named range with sheet scope, like this, for example: Name manager Ctrl+F3

Then you create additional function that to check if such name exists and get its value. Depending on the result the sheet will be hidden or not.

Public Function bIsVisible(ByRef wksSheet As Worksheet) As Boolean
    Dim bResult     As Boolean

    bResult = False
    On Error Resume Next
    bResult = Evaluate(wksSheet.Names("Visible").Value)

    bIsVisible = bResult
End Function

And here's to code for hiding the sheets (you can easily add here code for sheet protection etc.):

Public Sub HideUnvisible()
    Dim wksSheet        As Worksheet

    For Each wksSheet In Worksheets
        If Not bIsVisible(wksSheet) Then
            wksSheet.Visible = xlSheetHidden
        End If
    Next wksSheet
End Sub