Adding A Password to a Macro

[scott_86_]

Hi, I have two separate macros that work correctly withbuttons in the quick access toolbar to execute each independently.

They are 'Protect All Worksheets' & 'Unprotect All Worksheets.'

Can someone please help me to add a password just to the code 'Unprotect AllWorksheets' so that when it's button is pressed a password must beentered before executing.

Also, does anyone have a code to automatically 'Protect All Worksheets'when closing a workbook? (No password is needed here.)

For some reason I cannot attach an example file from my work computer. Thanksin advance.

Sub Unprotect_All()
Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.UnprotectPassword:="wts"
Next ws
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

 

[dmt32]

Hi,
see if this update to your code does what you want

Sub Unprotect_All()
    Dim ws As Worksheet
    Dim ProtectPassword As Variant
    
    On Error GoTo myerror
    
    Do
    ProtectPassword = InputBox("Enter Protection Password", "Password")
'cancel pressed
    If StrPtr(ProtectPassword) = 0 Then Exit Sub
    Loop Until Len(ProtectPassword) > 0
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:=ProtectPassword
    Next ws
    
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

You can use the Workbook_BeforeClose event to place code to protect sheets & save changes.


Untested but something like following maybe

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
'if changes made then apply sheet protection & save
    If Not Me.Saved Then
        For Each ws In ActiveWorkbook.Worksheets
            ws.Protect Password:="passwordhere"
        Next ws
        
        ThisWorkbook.Save
    End If


End Sub

Dave

 

[scott_86_]

Thank you!