Code Adjustment/Run Time Error

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top