VBA unprotect-blank entry and exit handling

Question

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

FaneDuru · Accepted Answer · 2021-07-19T12:22:41.767

InputBox behaves similarly for cases of pressing Cancel, inputting an empty string or pressing window corner x, in terms of String return.

The next sub gives a possibility to separate the above cases:

Sub testInputBox()
   Dim pass
    pass = InputBox("Password?")
    'standard behavior without checking the result:
    MsgBox "Pass is " & pass 'it will be an empty string in case of Cancel, empty string, pressing window corner X
    If StrPtr(pass) = 0 Then
        MsgBox ("Cancel pressed...") 'the same message if window corner `X` is pressed
    ElseIf pass = vbNullString Then
        MsgBox ("Empty string..")    'OK for an empty string
    Else
        MsgBox ("You entered " & pass)
    End If
End Sub

No need of error handling if you use the above way of identification.

And I should modify your code to act in this way:

Sub UnprotectAllSheets()
    Dim ws As Worksheet, pass As String, myPass As String, i As Long
    
        myPass = "1234"
TryAgain:
        pass = InputBox("Password?")
        If StrPtr(pass) = 0 Then Exit Sub 'for Cancel and window corner 'X' pressed
        If pass = vbNullString Then MsgBox "You did  not enter any password!", vbCritical, "Admin": Exit Sub
        If pass <> myPass Then
            
            MsgBox "Incorrect Password" & vbCrLf & _
                       IIf(i < 3, "Please, try again!", "We stop here..."), vbCritical, "Admin"
             If i >= 3 Then Exit Sub
            i = i + 1
           
            GoTo TryAgain
        End If
        For Each ws In ThisWorkbook.Worksheets
           If ws.ProtectContents = True Then
                ws.Unprotect pass
           End If
        Next ws
End Sub

I am driving now... I will adapt the code when I will be at home. But did I wrongly understand your question in terms of three attempts? — FaneDuru, Jul 19 '21 at 15:13
I have now managed to amend the code to remove the 3 attempts. I tried to promptly delete that comment before you read it. I was actually wondering why you had included the 3 attempts and now I see that there was a misunderstanding. The issue I was having with my code was that after a vbNullString or Cancel, it would present the default password window 3 times, which is not what I wanted. Thank you for you help :) — aye cee, Jul 19 '21 at 15:20

VBA unprotect-blank entry and exit handling

1 Answers1