worksheet combo box code runs when workbook open and close

Hello Excel experts,

I have very little experience with on sheet controls. It seems easy enough, but Excel seems to be so buggy. I am coding this with Excel 2002. I have combo box which selects one of many store locations. There is underlying vba code which populates one sheet and formats the outlines on that sheet. I have used worksheet_activate to reset the combobox's index and combobox_change to run the rest of the code.
So....
Should the code for combobox_change run on startup and before close? Well it does?
Shouldn't the combobox index=0 reset the combobox when the sheet opens? Well it doesn't?
Also, I had to change my sheet so there were no comments or other objects.
Why is that? Why don't any objects show up on my sheet when I search for them? Isn't the combobox an object?

Thanks for your help

Ed

Hi Ed,

I find it difficult to comment on this without seeing the workbook and the code behind it. Any chance you could upload it, or at least a representative sample if it's too large.

I'd be surprised if Excel was 'buggy', it's been around too long for that.


Rgds

Originally Posted by Kenneth Hobson

For the listindex property, set it to -1 if want nothing selected. 0 is the first item in the list.

I tried this and now I get a run-time error Object required
The basic problem is this combobox running it's macro code whenever it feels like it.

Thanks Again

Originally Posted by Richard Buttrey

Hi Ed,

I find it difficult to comment on this without seeing the workbook and the code behind it. Any chance you could upload it, or at least a representative sample if it's too large.

I'd be surprised if Excel was 'buggy', it's been around too long for that.
Rgds

The entire workbook is about 350k. I will warn you it is not a finished product. It is buggy. Enabling macros will change your Excel application a bit.

Thanks for any help you can offer

Ed

Originally Posted by Richard Buttrey

Hi Ed,

I find it difficult to comment on this without seeing the workbook and the code behind it. Any chance you could upload it, or at least a representative sample if it's too large.

I'd be surprised if Excel was 'buggy', it's been around too long for that.
Rgds

The entire workbook is about 350k. I will warn you it is not a finished product. It is buggy. Enabling macros will change your Excel application a bit.

Thanks for any help you can offer

Ed

You should press the Compile button and fix any compile errors. You should not use the characters "[" and "]" in sheet names.

When you typed your control name and the period, intellisense should have dropped a list of properties to pick from. You mis-typed your control name.

Please Login or Register  to view this content.

[QUOTE=Kenneth Hobson;2005126]
When you typed your control name and the period, intellisense should have dropped a list of properties to pick from. You mis-typed your control name.
Okay, this was a very dumb mistake to make. Thanks Kenneth

But from my original question, why does Excel run this code when I open and close this workbook? I am referring to the cboLocation_change(). I ask this because it now errors on the line Activeworkbooks.RefreshAll when I open the workbook, but works fine when I select a new location from the list. Also, on close of workbook, the line .Range("L6").Select errors. Does Excel run this Sub on open and close automatically?

Thanks,
Ed

As it stands now, you have an Activate event for the sheet with the combobox. By changing the ListIndex, you trigger the Change event for that control. In the Open and Close events for the Workbook, RouteMerge2006, you are activating the sheet which then triggers the Activate event for that sheet and so on.

To prevent this in the Close and Open events for the Workbook:

Please Login or Register  to view this content.