Q&A: Excel macro: Unlocking protected cells, prevent incorrect password error?

Question by iisjman07: Excel macro: Unlocking protected cells, prevent incorrect password error?
I’ve created a macro that counters one I created to protect cells with a password, and it works. However, if the password is incorrect, I get a runtime error asking if I want to debug, how can I stop this appearing and just have a message box saying the password is incorrect?

[QUOTE]Sub Unlockn()
If Worksheets(“Invoice”).ProtectContents = False Then
MsgBox Prompt:=”No password applied, cannot unlock.”, Title:=”Cannot Unlock”
End
End If

adminpass = InputBox(“Enter the administrative password to unlock…”, “Enter Password:”, “”)

If adminpass = “” Then
Exit Sub
End If

ActiveSheet.Range(“B18:B37”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“E18:E37”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“F18:F37”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“J1:M49”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“A1”).Select
MsgBox (“Unlocking Completed”)
End Sub
[/QUOTE]

Best answer:

Answer by dippu75
‘Try following procedure-
Sub Unlockn()

On Error GoTo errUlk

If Worksheets(“Invoice”).ProtectContents = False Then
MsgBox Prompt:=”No password applied, cannot unlock.”, Title:=”Cannot Unlock”
End
End If

adminpass = InputBox(“Enter the administrative password to unlock…”, “Enter Password:”, “”)

If adminpass = “” Then
Exit Sub
End If

ActiveSheet.Range(“B18:B37”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“E18:E37”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“F18:F37”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“J1:M49”).Select
ActiveSheet.Unprotect (adminpass)
ActiveSheet.Range(“A1”).Select
MsgBox (“Unlocking Completed”)

Exit Sub

errUlk:

If Err.Number = 1004 Then
MsgBox “Invalid password!”, vbCritical
Else
MsgBox Err.Description, vbCritical, “Error: ” & Err.Number
End If

End Sub

What do you think? Answer below!

Leave a Reply

Your email address will not be published. Required fields are marked *