Hi everyone,
here is a code that when opening the sheet and enabling macros, prompts you to enter a username and password. It then unlocks a certain sheet according to the username/password combination. I would like to add a code that would unlock all sheets of the workbook with the entry of an Admin account/password.
Thanks for your help
Workbook Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim w As Worksheet
Dim bSaveIt As Boolean
bSaveIt = False
For Each w In Worksheets
If w.Visible Then
Select Case w.Name
Case "Anie Arsenault"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Catherine Berube"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Gail Farmer"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Susan Flynn"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Laura Forsythe"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Elise Gauthier"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Philippe Gauthier"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Shirley Lavergne"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Aika Mistiva"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Manon St-Louis"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Celine Traversy"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Jean-Francois Boos"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Tyler Declare"
w.Protect ("password")
w.Visible = False
bSaveIt = True
Case "Jessica Kairouz"
w.Protect ("password")
w.Visible = False
bSaveIt = True
End Select
End If
Next w
If bSaveIt Then
ActiveWorkbook.CustomDocumentProperties("auth").Delete
ActiveWorkbook.Save
End If
End Sub
Private Sub Workbook_Open()
UserForm1.Show
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Monthly Stats" Then
If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then
Sh.Visible = False
MsgBox "Vous n'avez pas l'autorisation de voir cette page / You don't have authorization to view that sheet!"
End If
End If
End Sub
Userform Code
Dim bOK2Use As Boolean
Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean
bOK2Use = False
bError = True
If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
bError = False
Select Case txtUser.Text
Case "aarsenau"
sSName = "Anie Arsenault"
If txtPass.Text <> "password" Then bError = True
Case "gfarmer"
sSName = "Gail Farmer"
If txtPass.Text <> "password" Then bError = True
Case "Sflynn"
sSName = "Susan Flynn"
If txtPass.Text <> "password" Then bError = True
Case "lforsyth"
sSName = "Laura Forsythe"
If txtPass.Text <> "password" Then bError = True
Case "egauthie"
sSName = "Elise Gauthier"
If txtPass.Text <> "password" Then bError = True
Case "pgauthie"
sSName = "Philippe Gauthier"
If txtPass.Text <> "password" Then bError = True
Case "slavergn"
sSName = "Shirley Lavergne"
If txtPass.Text <> "password" Then bError = True
Case "amistiva"
sSName = "Aika Mistiva"
If txtPass.Text <> "password" Then bError = True
Case "mstlouis"
sSName = "Manon St-Louis"
If txtPass.Text <> "password" Then bError = True
Case "ctravers"
sSName = "Celine Traversy"
If txtPass.Text <> "password" Then bError = True
Case "jboos"
sSName = "Jean-Francois Boos"
If txtPass.Text <> "password" Then bError = True
Case "tdeclare"
sSName = "Tyler Declare"
If txtPass.Text <> "password" Then bError = True
Case "jkairouz"
sSName = "Jessica Kairouz"
If txtPass.Text <> "password" Then bError = True
Case "cberube"
sSName = "Catherine Berube"
If txtPass.Text <> "password" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If
Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate
bOK2Use = True
Unload UserForm1
End If
End Sub
Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub
Bookmarks