CODEWITHSUNDEEP
excelvbaviewpasswords
Chopin
Chopin

Reputation: 214

Access password blocked sheet - Excel

The code below restricts access by hiding a sheet unless a password is entered. If it is entered correctly, the sheet can be viewed from the individual tabs. However, it won't let me view and then edit the sheet.

Can this be adjusted to allow the user to enter a password and then view and edit the sheet?

Private Sub Workbook_Open()
Sheets("Sheet1").Visible = xlSheetHidden
End Sub

Public ViewAccess As Boolean       'In restricted sheet's activate event
Private Sub Worksheet_Activate()
If ViewAccess = False Then
Me.Visible = xlSheetHidden
Response = Application.InputBox("Password", xTitleId, "", Type:=2)
    If Response = "123" Then
        Me.Visible = xlSheetVisible
        Application.EnableEvents = True
        ViewAccess = True
    End If
End If
End Sub

Upvotes: 0

Views: 123

Answers (1)

Harun24hr
Harun24hr

Reputation: 36780

Following code will help you. When a user will select a sheet with name HiddenSheet it will ask for password. If password is correct then it will allow for editing data otherwise will go to previous sheet automatically, You have to change HiddenSheet for your sheet name.

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

MySheetName = "HiddenSheet" 'The sheed which I want to hide.

If Application.ActiveSheet.Name = MySheetName Then
    Application.EnableEvents = False
    Application.ActiveSheet.Visible = False
    response = Application.InputBox("Password", "Enter Password", "", Type:=2)

        If response = "123456" Then 'Unhide Password.
            Application.Sheets(MySheetName).Visible = True
            Application.Sheets(MySheetName).Select
        End If
End If

Application.Sheets(MySheetName).Visible = True

Application.EnableEvents = True
End Sub

Code snipped:

enter image description here

Upvotes: 3

Related Questions