i have 5 worksheets in my excel workbook but i want to specifically protect 3rd and 5th worksheets with password

Question

I have 5 worksheets in my excel workbook but I want specifically protect 3rd and 5th worksheets with password. Have below macro which protects only one sheet. what needs to be added to protect more than one sheet but not all sheets. Kindly guide me.

Thanks in Advance!!

      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 = "pass" Then
             Sheets(MySheet).Visible = True
             Application.EnableEvents = False
             Sheets(MySheet).Select
              Application.EnableEvents = True
              End If
       End If
       Sheets(MySheet).Visible = True
 End Sub

Plagon Plagon · Accepted Answer · 2017-07-06T11:42:03

Try this:

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

But hardcoding the password doesnt make it that safe.

i have 5 worksheets in my excel workbook but i want to specifically protect 3rd and 5th worksheets with password

1 Answers