Hide/Unhide Worksheets Based on Name & Password
Greetings,
I found the code below and it gets me moving in the right direction but I would like to have significantly more autonomy over the users and which worksheets they can access within a spreadsheet. Here is the code:
Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
pword = InputBox(“Enter logon information to access permitted worksheets”)
Select Case pword
Case Is = “TEST”: Sheets(“NOT MANAGER”).Visible = True
Case Is = “MANAGER”: Call UnHideAllSheets
End Select
Sheets(“Dummy”).Visible = False
Exit Sub
endit:
MsgBox “Incorrect Password”
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets(“Dummy”).Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> “Dummy” Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub
Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub
I like the idea of a dummy worksheet that the spreadsheet uses but I would like to incorporate a username in conjunction with a password to give access to users based on what they need access to. Additionally, rather than having to change usernames and passwords in VBA, I would like to have a hidden worksheet that an administrator can go in to add/delete/modify usernames, passwords and the worksheets each user has access to.
Not sure if this is possible but if so it is definitely beyond me and I would appreciate any assistance.
Thanks,
C
Greetings, I found the code below and it gets me moving in the right direction but I would like to have significantly more autonomy over the users and which worksheets they can access within a spreadsheet. Here is the code: Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
pword = InputBox(“Enter logon information to access permitted worksheets”)
Select Case pword
Case Is = “TEST”: Sheets(“NOT MANAGER”).Visible = True
Case Is = “MANAGER”: Call UnHideAllSheets
End Select
Sheets(“Dummy”).Visible = False
Exit Sub
endit:
MsgBox “Incorrect Password”
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets(“Dummy”).Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> “Dummy” Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub I like the idea of a dummy worksheet that the spreadsheet uses but I would like to incorporate a username in conjunction with a password to give access to users based on what they need access to. Additionally, rather than having to change usernames and passwords in VBA, I would like to have a hidden worksheet that an administrator can go in to add/delete/modify usernames, passwords and the worksheets each user has access to. Not sure if this is possible but if so it is definitely beyond me and I would appreciate any assistance. Thanks,C Read More