CODEWITHSUNDEEP
excelvba
Noob_Programmer
Noob_Programmer

Reputation: 147

Excel Password protected sheet

I am using this code to password protect two pages.
For some weird reason I can hide "Sheet1" but not "Sheet2" as its always visiable.
The reason for the line Sheets(MySheet2).Visible = True is so if someone puts in the wrong password it won't just hide it instantly.

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

Upvotes: 0

Views: 166

Answers (1)

user3598756
user3598756

Reputation: 29421

I guess you're after this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim MySheets As String, Response As String

    With ActiveSheet
        Select Case .Name
            Case "Sheet1", "Sheet2"
                Application.EnableEvents = False
                .Visible = False
                Response = InputBox("Enter password to view sheet")
                If Response = "MyPass" Then
                    .Visible = True
                    .Select
                End If
                Application.EnableEvents = True
            End Select
    End With
End Sub

as you should already know, this code is to be placed in ThisWorkbook code pane

Upvotes: 2

Related Questions