I use Excel 97 on Windows NT4. I have a monthly mileage spreadsheet to
produce, where users enter mileage day by day. At the end of the month
they submit the sheet by pressing a button. The button

a. changes the font colour of the data they have input to signify that
the data has been processed

b. makes the sheet read-only by applying locking to all cells and then
protecting them

My button code is

Sheet1.Range("A1:F8").Font.ColorIndex = 3
.... ' lock cells
.... ' protect sheet

I get the error message "Could not set the ColorIndex property of the
Range class". As the cells are not locked and the sheet is not
protected, I am at a loss to explain why the Range settings appear to
be read-only. If I do

Sheet1.Range("A1:F8").Formula = "= rand() * 10000"

I get random numbers with no problems. In other words it appears that
I can set "background" properties OK but not "foreground" (ie UI)
properties. I tried altering FormatConditions for the cells and got a
similar error message. Can anyone help with this? Thanks a lot in
advance...


----------------------------------------------------
Do NOT reply to the email address in the headers. It
is an address used solely for trapping spam and its
contents are never checked. If you want to reply to
this message, please post to the newsgroup.
----------------------------------------------------

Assume this is a commandbutton from the Control toolbox toolbar. Change the
TakeFocusOnclick property of the commandbutton to False.

--
Regards,
Tom Ogilvy


Alan Greenwood wrote in message
...
I use Excel 97 on Windows NT4. I have a monthly mileage spreadsheet to
produce, where users enter mileage day by day. At the end of the month
they submit the sheet by pressing a button. The button

a. changes the font colour of the data they have input to signify that
the data has been processed

b. makes the sheet read-only by applying locking to all cells and then
protecting them

My button code is

Sheet1.Range("A1:F8").Font.ColorIndex = 3
... ' lock cells
... ' protect sheet

I get the error message "Could not set the ColorIndex property of the
Range class". As the cells are not locked and the sheet is not
protected, I am at a loss to explain why the Range settings appear to
be read-only. If I do

Sheet1.Range("A1:F8").Formula = "= rand() * 10000"

I get random numbers with no problems. In other words it appears that
I can set "background" properties OK but not "foreground" (ie UI)
properties. I tried altering FormatConditions for the cells and got a
similar error message. Can anyone help with this? Thanks a lot in
advance...


----------------------------------------------------
Do NOT reply to the email address in the headers. It
is an address used solely for trapping spam and its
contents are never checked. If you want to reply to
this message, please post to the newsgroup.
----------------------------------------------------

Great stuff, Tom - thanks a lot.

On Sat, 1 Nov 2003 08:31:34 -0500, "Tom Ogilvy"
wrote:

Assume this is a commandbutton from the Control toolbox toolbar. Change the
TakeFocusOnclick property of the commandbutton to False.

----------------------------------------------------
Do NOT reply to the email address in the headers. It
is an address used solely for trapping spam and its
contents are never checked. If you want to reply to
this message, please post to the newsgroup.
----------------------------------------------------