Hi everyone, I am trying to come up with a method for temporarily storing a password. The file that I am creating is going to be used by numerous people, only some of whom will have full access to the entire document. I have code (part of which is below) that I use to save the workbook when an authorized user makes changes to the workbook. There are 4 tabs in total in the workbook, and the saving code is assigned to a button on the 4th tab. The 4th tab is hidden and the entire workbook is password protected, while the first 3 tabs are individually password protected at the sheet level. My concern is that an authorized user will unlock the workbook, make legitimate changes, and run the save code but use a different password than is used at the sheet level. For instance, if the password for the sheets is "Password1", but the user saves the workbook using the password "Password2", then the sheets will still use "Password1". I want to make sure that the same password is used to protect individual sheets and the workbook itself. In order to access the button to save changes made by an authorized user, the user must at a minimum unlock the workbook. I would like to have VBA automatically unlock all sheets with the workbook level password so that when the user saves, the new password is applied to the workbook and each of the worksheets. Is there some sort of AfterUnprotect event that can store the password, place it in a string variable, and then unlock all the sheets with the string variable?
Code:
Sub ManagementSave()
Dim PWInitial$, PWConfirm$, i%
PWInitial = InputBox("Please enter the password to protect this workbook." & Chr(10) & Chr(10) & _
"Remember that passwords are case sensitive and cannot be recovered if lost or forgotten.", _
Title:="Workbook Password")
If PWInitial = vbNullString Then Exit Sub
PWConfirm = InputBox("Please confirm the password, ensuring that case sensitivity is preserved.", _
Title:="Confirm Password")
If PWConfirm = vbNullString Then Exit Sub
If PWInitial <> PWConfirm Then
MsgBox "The two passwords did not match. Please retry entering the password.", Title:="Password Mismatch"
Exit Sub
End If
Sheets("Lists").Visible = False
ActiveWorkbook.Protect Password:=PWConfirm, Structure:=True, Windows:=False
For i = 1 To 3
Sheets(i).Protect Password:=PWConfirm, DrawingObjects:=True, Contents:=True, Scenarios:=True
Next i
End Sub
