Unable to set the colorIndex Property of the Interior Class Error
 

Hi.

I have an Excel VBA Form which runs thru a selected range's cells and
sets the ColorIndex values.

The code works fine for the 1st 2 to 3 calls, but then after that it
errors with "1004 Unable to set the colorIndex Property of the
Interior Class Error" Error Message.

I don't understand what is exactly going wrong as, when it bombs and
should be trapped by my error handling, I never see a breakpoint etc.
It seems as if Excel has not passed the Error back to the VBA IDE.

Has anyone else found problems with regards to setting the ColorIndex
values repeatedly via VBA? If so, how did you get around this?

Thanks

Unable to set the colorIndex Property of the Interior Class Error
 

I believe the cell is locked with sheet protection

try to have a look at the properties
menu bar - tools - protection - unprotect the sheet


"GriffyGriff" wrote in message
om...
Hi.

I have an Excel VBA Form which runs thru a selected range's cells and
sets the ColorIndex values.

The code works fine for the 1st 2 to 3 calls, but then after that it
errors with "1004 Unable to set the colorIndex Property of the
Interior Class Error" Error Message.

I don't understand what is exactly going wrong as, when it bombs and
should be trapped by my error handling, I never see a breakpoint etc.
It seems as if Excel has not passed the Error back to the VBA IDE.

Has anyone else found problems with regards to setting the ColorIndex
values repeatedly via VBA? If so, how did you get around this?

Thanks

Unable to set the colorIndex Property of the Interior Class Error
 

If it works 3 times on the same cells and you don't protect the sheet in
your code, then protection might not be the problem.

What version of Excel. If using Excel 97, and running it from a
commandbutton, try changing the TakeFocusOnClick property to false.

If it is another control on the useform that triggers the code, then place
ActiveCell.Activate as the first line of code in the event.

If you have included an error handler, do you have a resume statement in the
error handler. If not, then once an error occurs, you are remaining in
error handling mode and a second error will cause excel to quit since you
have experience an error in the error handler. In the error handler, you
must have some form of resume statement to get out of error handling mode
and show that the error has been handled.

See Excel VBA help on the resume statement for a full explanation.

--
Regards,
Tom Ogilvy

"GriffyGriff" wrote in message
om...
Hi.

I have an Excel VBA Form which runs thru a selected range's cells and
sets the ColorIndex values.

The code works fine for the 1st 2 to 3 calls, but then after that it
errors with "1004 Unable to set the colorIndex Property of the
Interior Class Error" Error Message.

I don't understand what is exactly going wrong as, when it bombs and
should be trapped by my error handling, I never see a breakpoint etc.
It seems as if Excel has not passed the Error back to the VBA IDE.

Has anyone else found problems with regards to setting the ColorIndex
values repeatedly via VBA? If so, how did you get around this?

Thanks

Unable to set the colorIndex Property of the Interior Class Error
 

Thanks Tom.

Firstly, yes you are right, because this runs OK for the 1st few times
it it NOT a protection Issue.

We are using Excel 2,000.

We have tried setting the Buttons attribute "TakeFocusOnClick" = False
AND ActiveCell.Activate was the 1st Line (Even with a "DoEvents()"
thrown in on line 2, for good measure), all to no avail.

The Routine in question does have an "On Error Goto FunctionA_Err"
Handling Routine, with a Resume FunctionA_Out statement. But even
with a breakpoint Set ON, BEFORE the Resume statement, The VBA IDE
Degugger does not stop on any line in the Error Handling Routine. It
seems like Excel has not handed me back control before it bomed. (The
following comment is not intended to offend anyone, but it has nothing
to do with me making a mistake debugging (as I have being doing VBA
for yonks)).

Hope someone can shed some light on why multiple calls to change a
cell's ColorIndex should fail after 2/3 attempts, whilst using EXACTLY
the SAME VBA Function.

Thanks Again.