Assign Change event to generic ComboBox

I have two comboboxes which are programmically created and i want one's contents to be changed when a value is selected in the other. I know how to accomplish this by writing a _Change() macro for each combobox, but this requires that i know that name of the combobox. For instance i could write for combobox1, Private Sub combobox1_Change() .... and it would run the code upon a change in the control for combox1, but since i am programmically creating these comboboxes, and there could be many of these comboboxes, i need a way to attach a change event macro without using the combobox's name. Or perhaps a clever way of solving this with names.


comboboxexample.png

Have you tried googling the title of you post with VBA in front of it? There are plenty of examples out there.


Sent from my iPad using Tapatalk

Hi pjcpm9,

See the attached file which is an example using 'Active X' combo boxes. It uses a Class to handle all the Combo Box events. Key points in the code would not have been possible without previous posts by Andy Pope and Chip Pearson.

Lewis

Class code in Class Module 'ClassComboBoxEvent'

Please Login or Register  to view this content.

Ordinary Module Code:

Please Login or Register  to view this content.

I still dont understand how to add an event like _Change to an OLEObject like a combobox that isn't specific to a named combobox like "combobox32".

It's difficult to explain, so try the attached file which is specific to 'ComboBox32'.

a. The generic 'myComboBox_Change()' in the Class traps 'ComboBox' changes for all those ComboBoxes 'enabled' in the 'Enable Routine'.
b. Generally I have the generic '_Change' routine call an 'Event Handler' routine in an ordinary module such as 'ProcessComboBoxChangeEvent()'.
c. 'ProcessComboBoxChangeEvent() identifies which 'ComboBox' had it's value changed and takes appropriate action.

Modified Class Module Code for class 'ClassComboBoxEvent':

Please Login or Register  to view this content.

Event Handler code in an ordinary code module:

Please Login or Register  to view this content.

If you still have questions or problems, please ask.

Lewis

Ok i've been messing around with this code for some time now, trying to reverse engineer it if you will. I don't understand why you have to run the 'enableevents' macro separately from the 'createcombobox' macro. I even went so far as making a module that runs both, but the events trigger doesn't fire. It only fires when the eventsenabled macro is run separately altogether. What i need is a way to create a combobox and enable events for that combobox simultaneously

I don't understand why you have to run the 'enableevents' macro separately from the 'createcombobox' macro.

I don't understand either. When I wrote the code, I was unsuccessful doing both at the same time.

What i need is a way to create a combobox and enable events for that combobox simultaneously

I don't think I ever needed that capability.

I need to create the combobox at design time, which is a one time only item.

At runtime, I need to populate the ComboBox and Enable Events which I am able to do at the same time.

------

If you are still having problems after a couple of days, I suggest you close this thread (because it will probably not generate any more responses for you), and open a new thread with a reference (link) to this thread.

I'm sorry I wasn't able to help more.

Lewis

I figured it out! thanks guys!

Untitled.pngUntitled1.png

I think the Application.Ontime allows both to be accomplished at the same time

Originally Posted by LJMetzger

I don't understand either. When I wrote the code, I was unsuccessful doing both at the same time.




I don't think I ever needed that capability.

I need to create the combobox at design time, which is a one time only item.

At runtime, I need to populate the ComboBox and Enable Events which I am able to do at the same time.

------

If you are still having problems after a couple of days, I suggest you close this thread (because it will probably not generate any more responses for you), and open a new thread with a reference (link) to this thread.

I'm sorry I wasn't able to help more.

Lewis

@LJMetzger, just out of interest, can you test the attached modification of your document?
I modified it to add the controls and populate the collection in one loop.
I found that minimising the references to the ActiveWorksheet object fixed the problem.

Generally speaking the spreadsheet will be re-built whenever a control is added and this causes the global variables to be reset if they carry references to the worksheet.
Any objects, including collections that are in the WorkSheet module will be reset. Normally, if you have the code in a standard module it will be OK.

With this in mind I added this line to your CreateComboboxes routine...

Please Login or Register  to view this content.

and then replaced any references to ActiveSheet with ws in the code.
I then added in the extra lines to manage the collection like so...

Please Login or Register  to view this content.

And it works reliably.

coolblue,

I found that minimising the references to the ActiveWorksheet object fixed the problem.

Generally speaking the spreadsheet will be re-built whenever a control is added and this causes the global variables to be reset if they carry references to the worksheet.
Any objects, including collections that are in the WorkSheet module will be reset. Normally, if you have the code in a standard module it will be OK.

Thanks for the clarification. It also seems like a reason to create Worksheet objects more often. Your fix works great.

Thanks again.

Lewis

Thanks for taking the time to test that Lewis, I think its got something to do with late binding... the link you provided in the sheet on this topic gives some clues perhaps. If you have references to a late bound bound object (ActiveSheet) in the OLEObjects.Add line, then it makes sense that the module containing this line will be dragged into the re-complile that is triggered by adding controls to the sheet surface. So limiting references to that object helps.

It's not 100% reliable though, it won't work if you have VBE open and have just complied (from the Debug Menu). But its fine on subsequent runs.

The onTime approach is a clever work-around that delays loading the Collection until after VBA has finished its house-keeping; including recompiling the sheet where the controls were added.
I'm not sure how reliable it is yet though and I'm a bit uncomfortable with it.

The safest way to do it is to initiate the build from a key stroke, not from a button on the same sheet.
As long as VBE is closed, this seems to work every time (except for the first time you run it after compiling and closing VBE).
It will also work OK if triggered from an Application Event (e.g. BeforeSave).
Again, the idea is to keep references to the target sheet away from the code.

This is demonstrated in the attached version which I have knocked into an object oriented approach just to give some variety.
LJMComboBoxActiveX-2014-07-20.xls