Unprotect sheet prompt for PW only when AllowFiltering = True

Question

I have two very simple subs to protect and unprotect a sheet with a password. The Subs work perfectly fine until I add the "AllowFiltering:=True" Parameter.

When that parameter is added, I am prompted for a password when unprotecting the sheet. However, if I press Cancel, the sheet is unprotected (as it should be).

Hence, the password prompt seems completely redundant, but can someone help me get rid of it?

The two subs are:

Sub LockSheet()

If ActiveSheet.Protect = False Then
ActiveSheet.Protect Password:="TopSecretPW", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
End If

End Sub


Sub UnlockSheet()

If ActiveSheet.Unprotect = False Then
ActiveSheet.Unprotect Password:="TopSecretPW"
End If

End Sub

If I use this macro - without the AllowFiltering:=True parameter - I am not prompted for a pw.

Sub LockSheet()

If ActiveSheet.Protect = False Then
ActiveSheet.Protect Password:="TopSecretPW", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

End Sub

Bonus info: The filter and protection is working just fine with the parameter set, and I cannot unprotect the sheet from the menu without entering the correct PW. It is just that uninvited PW prompt that I want to bury.

DHL-JDParker DHL-JDParker · Accepted Answer · 2016-05-24T18:46:54

The problem is with how you are checking if the sheet is protected. ActiveSheet.Protect is a method, not a property of the worksheet object. Instead of using If ActiveSheet.Protect use If ActiveSheet.ProtectContents. ProtectContents is a property of the WorkSheet object and will return true if the sheet is protected and false if it is not. Your revised code should look like this:

Sub LockSheet()

If ActiveSheet.ProtectContents = False Then
    ActiveSheet.Protect Password:="TopSecretPW", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
End If

End Sub


Sub UnlockSheet()

If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect Password:="TopSecretPW"
End If

End Sub

Unprotect sheet prompt for PW only when AllowFiltering = True

1 Answers