Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
unable to get the pivotfields property of the pivottable class dhstein Excel Discussion (Misc queries) 0 January 6th 10 03:27 AM
Unable to get the Interior property of the PlotArea class [email protected] Charts and Charting in Excel 2 September 3rd 08 03:41 PM
Unable to set the XValues property of the Series class Ben Charts and Charting in Excel 7 December 7th 06 11:01 PM
VBA error: Unable to set the Values property of the Series class Marco Shaw Charts and Charting in Excel 1 July 12th 05 02:34 PM
Unable to set the Locked Property of the Range Class Stuart[_5_] Excel Programming 0 July 15th 03 06:59 PM


All times are GMT +1. The time now is 07:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"