Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to get the pivotfields property of the pivottable class | Excel Discussion (Misc queries) | |||
Unable to get the Interior property of the PlotArea class | Charts and Charting in Excel | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
VBA error: Unable to set the Values property of the Series class | Charts and Charting in Excel | |||
Unable to set the Locked Property of the Range Class | Excel Programming |