Protecting worksheet cause vba code to fail

[Ken Warthen]

I have the following code in the Worksheet_Change event of a worksheet.

Case "$E$14" 'Approval status
If Target.Value <> "" Then
If Target.Value = "Not Submitted" Then
Target.Font.Color = 255
Else
Target.Font.Color = 0
End If
End If

The code works fine, until I turn on the Protect Sheet feature. Then it
generates an error code 1004 (Application-defined or object-defined error).
Does anyone know why protecting the worksheet would cause the code to fail?

Ken

 

[Nick H]

Hi Ken,

This is probably because the 'AllowFormattingCells' property is set to
false.

If you are manually protecting the sheet you can put a tick next to
'Format Cells' in the Protect Sheet dialog.

HTH, Nick H

 

[Mike H]

Ken,

Unprotect at runtime and the re-protect

Case "$E$14" 'Approval status
If Target.Value <> "" Then
ActiveSheet.Unprotect Password:="MyPass"
If Target.Value = "Not Submitted" Then
Target.Font.Color = 255
Else
Target.Font.Color = 0
End If
ActiveSheet.Protect Password:="MyPass"
End If

Mike

 

[Ken Warthen]

Mike,

That doesn't seem very practical since anytime any cell value changes the
code runs.

Ken

 

[Dave Peterson]

But that portion of the code would only run if you've changed E14 and E14 is not
"". And the worksheet would be unprotected for a very short period of time.

And since this in in the worksheet_change event (I'd guess), I'd do:

Case "$E$14" 'Approval status
If Target.Value <> "" Then
me.unprotect Password:="MyPass"
If lcase(Target.Value) = lcase("Not Submitted") Then
Target.Font.Color = 255
Else
Target.Font.Color = 0
End If
me.protect password:="MyPass"
End If

The Me keyword refers to the object owning the code. In this case, I'm guessing
it's the worksheet getting the change.

And I'd watch out for upper/lower case differences in the string comparison.

 

[Nigel]

I presume your code snippet and the remainder of the select statements are
part of your worksheet change event. The code can be controlled for all
cells, or selective cells. Is it only cell E14 or others you wish to
control?



--

Regards,
Nigel
(e-mail address removed)

 

[Ken Warthen]

Nick,

That's the answer I was looking for. Thanks for the help.

Ken

 

[Nigel]

Assuming you allow any cell by any user to change the format?

--

Regards,
Nigel
(e-mail address removed)