lundi 19 juillet 2021

VBA unprotect-blank entry and exit handling

The below code unprotects all sheets in the workbook and prompts for the password only once.

What I am trying to achieve is:

If user presses "cancel" on the password input window, the sub exits. If user presses "ok" without entering anything, it should behave in the same way as entering the wrong password i.e. go to the error popup.

The issue is on pressing "ok" or "cancel" it doesnt behave as above, rather, in both cases, it brings up the default password prompt 3 more times, 1 for each sheet.

I am struggling with perfecting the if/then logic and have swapped things around many times, almost getting there but never quite.

Sub UnprotectAllSheets()
    Dim ws As Worksheet
    Dim pass As String
    
    If ActiveSheet.ProtectContents = False Then
        MsgBox "Already Unprotected" 
        Exit Sub
    Else

        pass = InputBox("Password?")
        On Error GoTo Popup:
        
        For Each ws In ThisWorkbook.Worksheets
            ws.Unprotect pass
        Next ws
        
        If ActiveSheet.ProtectContents = False Then
            MsgBox "Sheets now Unprotected"
            
        ElseIf StrPtr(pass) = "" Then  'if press OK on blank entry
            MsgBox "Incorrect Password", vbCritical, "Admin"
            
        ElseIf pass = 0 Then 'if press CANCEL
            Exit Sub
        End If
    End If
Popup:
    If err.Number = 1004 Then
        MsgBox "Incorrect Password", vbCritical, "Admin"
    End If
End Sub

Aucun commentaire:

Enregistrer un commentaire