Problems with worksheet containing ActiveX controls
 

I am running into lots of difficulties with a worksheet
that contains ActiveX controls. The situation is the following:
1) Worksheet contains, say, a combo box and corresponding
*_Change routine that modifies cells in ActiveSheet
2) Excel is in Automatic Calculation mode

When I try to save the spreadsheet, Excel seems to execute
some of the *_Change routines (why?) with the "wrong" ActiveSheet.
This, of course, creates an error condition.

I tried to put in error handling routines that would just
get out of the *_Change subroutines if an error occurred.
This eventually led to spreadsheets that crash Excel.

I also tried referring to the right worksheet using the Parent property
of the control. This also leads to error conditions that I do not understand.

Can anyone provide help or insight?

Problems with worksheet containing ActiveX controls
 

Thanks for the advice. I basically want to write code for the controls
so that the worksheet can be copied and still work properly.

But I've noticed that when I copy the worksheet, funny things happen
to the original worksheet. And sometimes Excel just crashes.

And this is just due to ActiveX controls in a worksheet?

(onedaywhen) wrote in message . com...
Try explicitly refering to the worksheet in question i.e. instead of

ActiveSheet.Range("A1")

try

Worksheets("Sheet1").Range("A1")

If you are using 'ActiveSheet' because you want a generic reference
(e.g. you use the same code on different sheets and you want to copy
and paste the code) try using the Me keyword to refer to the current
instance of the 'sheet' class i.e.

Me.Range("A1")

If you are using 'generic' code you could consider using a Withevents
ActiveX object in a class but this doesn't always work - I'm coming
round to the opinion that a worksheet makes a lousy container for
ActiveX controls...

(Deane Yang) wrote in message . com...
I am running into lots of difficulties with a worksheet
that contains ActiveX controls. The situation is the following:
1) Worksheet contains, say, a combo box and corresponding
*_Change routine that modifies cells in ActiveSheet
2) Excel is in Automatic Calculation mode

When I try to save the spreadsheet, Excel seems to execute
some of the *_Change routines (why?) with the "wrong" ActiveSheet.
This, of course, creates an error condition.

I tried to put in error handling routines that would just
get out of the *_Change subroutines if an error occurred.
This eventually led to spreadsheets that crash Excel.

I also tried referring to the right worksheet using the Parent property
of the control. This also leads to error conditions that I do not understand.

Can anyone provide help or insight?