Excel Password protected sheet

Question

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

are there only two sheets in the workbook? I haven't tested your code but I would imagine that you are trying to hide both sheets and hence the error. — Miguel
– Miguel, Commented Mar 28, 2017 at 9:03

user3598756 · Accepted Answer · 2017-03-28 09:04:42Z

2

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

answered Mar 28, 2017 at 9:04

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Noob_Programmer Over a year ago

This works Very well excpet one thing, if someon enters the wrong password then the sheet becomes hidden. And then its inaccsessable without the need to manually unhide it

user3598756 Over a year ago

and what would be the wanted behavior in that case?

Noob_Programmer Over a year ago

Just unable to acsess the sheet, and when trying again it asks for the password again.

user3598756 Over a year ago

the question is still the same: what would the wanted behavior be?

Ralph Over a year ago

(+1) from my side. @Noob_Programmer: I am assuming what user3598756 is trying to get to is: either the sheet is shown or it is hidden. There is nothing in between. If it is shown then you can see it (with or without password). That doesn't mean that you can change anything in that sheet. Yet, it is not hidden and thus all information on the sheet is "available" to look at. So, what shall it be (in the case that an incorrect password is entered): hide the sheet or show it?

Collectives™ on Stack Overflow

Excel Password protected sheet

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related