Protecting worksheet cause vba code to fail
 

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

Protecting worksheet cause vba code to fail
 

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

Protecting worksheet cause vba code to fail
 

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" wrote:

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

Protecting worksheet cause vba code to fail
 

Mike,

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

Ken

"Mike H" wrote:

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" wrote:

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

Protecting worksheet cause vba code to fail
 

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.

Ken Warthen wrote:

Mike,

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

Ken

"Mike H" wrote:

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" wrote:

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

--

Dave Peterson

Protecting worksheet cause vba code to fail
 

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




"Ken Warthen" wrote in message
...
Mike,

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

Ken

"Mike H" wrote:

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" wrote:

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

Protecting worksheet cause vba code to fail
 

Nick,

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

Ken

"Nick H" wrote:

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

Protecting worksheet cause vba code to fail
 

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

--

Regards,
Nigel




"Ken Warthen" wrote in message
...
Nick,

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

Ken

"Nick H" wrote:

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