Code Adjustment/Run Time Error

[Anthony G.]

The following code that Mark provided for me works quite well...until I protect the worksheets. When protected, I receive the following message: Run-time error 1004 - Unable to set the Bold property for the font class.

Here's the formula
Private Sub Worksheet_Change(ByVal Update As Excel.Range)
If Intersect(Update, Range("q13:z137")) Is Nothing Then Exit Sub
With Update
.Font.Bold = True
.Font.Italic = True
With .Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End With
End Sub


Any suggestions?

Thanks,

Anthony

[RET79]

A quick guess....

You are trying to alter something on a protected sheet... I thought the whole point of protecting a sheet was that you couldnt alter stuff in it.

RET79

[Corticus]

You could try this:


Private Sub Worksheet_Change(ByVal Update As Excel.Range)
Range("q13:z137").Select
Selection.Locked = False
If Intersect(Update, Range("q13:z137")) Is Nothing Then Exit Sub
With Update

Range("q13:z137").Select
Selection.Locked = False

.Font.Bold = True
.Font.Italic = True
With .Interior
.ColorIndex = 36
.Pattern = xlSolid

Range("q13:z137").Select
Selection.Locked = True

End With
End With
End Sub

[James]

Have a look at what was posted earlier toay
"Cell Protection"

James

[Anthony G.]

I applied the adjusted formula and it returned the following message:

Unable to set the locked property of the range class.

Any other suggestions?

Thanks,

Anthony

[James]

Try something along thes lines:

Private Sub CommandButton1_Click()
With Selection
Sheets("YOUR SHEET NAME HERE").Unprotect password:="YOUR PASSWORD"
YOUR CODE HERE
End With
Sheets("YOUR SHEET NAME HERE").Protect
password:="YOUR PASSWORD"
End Sub

James

[Anthony G.]

I attempted to adjust the following formula according to instructions provided to me by James. I do not know what I'm doing wrong for I continue to receive the following error message: Complie error - Syntax Error, which is refering to the second to the last line of the code.

Here's the formula:

Private Sub CommandButton1_Click()
With Selection
Sheets("Oklahoma").Unprotect password:="SWNB"
Private Sub Worksheet_Change(ByVal Update As Excel.Range)
Range("q13:z137").Select
Selection.Locked = False
If Intersect(Update, Range("q13:z137")) Is Nothing Then Exit Sub
With Update

Range("q13:z137").Select
Selection.Locked = False

.Font.Bold = True
.Font.Italic = True
With .Interior
.ColorIndex = 36
.Pattern = xlSolid

Range("q13:z137").Select
Selection.Locked = True

End With
End With
Sheets("Oklahoma").Protect
password:="SWNB"
End Sub

Any suggestions?

Thanks,

Anthony

[James]

Hi Anthony,

Here's the code, copy and paste in your sheet1, change sheet name & cell address to suit:

Private Sub Worksheet_Change(ByVal Update As Range)

Sheets("Sheet1").Unprotect Password:="Anthony"

If Intersect(Update, Range("A1:A5")) Is Nothing Then Exit Sub

With Update
Range("A1:A5").Select

.Font.Bold = True
.Font.Italic = True
With.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
End With
Sheets("Sheet1").Protect Password:="Anthony"
End Sub

James

_________________


[ This Message was edited by: James on 2002-05-09 13:30 ]

[ This Message was edited by: James on 2002-05-09 13:47 ]

[Anthony G.]

Hey James!!! Thanks again for the response. It works very well, just one more thing though: when I apply the formula, protect the sheet and attempt to make a change, it does in fact work, however, upon formatting that one specific cell as per the code, the entire range becomes highlighted. Is that supposed to happen?

All the Best

Anthony