asked on

VBA handle multiple combobox change event

I have around 30+ combo boxes on my form, i hide the ones i dont require when form is loaded.

I need to track change event of each combobox, based on which the drop down list of each CB is altered.

I suspect this can be done by creating class event, but i dont know how to go forward with this.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cynx

ASKER

I created a change event as adviced.
While i initialize my form, I am adding the all combo boxes to a collection after setting the combobox object.

I am able to call the routine defined in class module with the combo box change event, which has a msgbox.

Now, instead of that message box, i am calling another routine, which should use the value in active combobox to perform few actions.
How can i access the combobox which was changed and its value ?

cynx

ASKER

to provide more info on this,

for example the combo box on my form has default value of "A". and the user is selecting "B" from drop down list.
I need to use this value A and B in my new routine

Rory Archibald

Have your called routine take a combobox as an argument and pass the combobox from the class to the routine.
For example:

Sub CalledRoutine(cbo as MSForms.Combobox)
' do stuff with cbo
End Sub

In the class module, assuming m_cbo is your withevents combo variable:

Private Sub m_cbo_Change()
calledroutine m_cbo
End Sub

cynx

ASKER

Thanks that worked, however, How do i capture the before change value of combo box ? as change event returns the new selected value.

Rory Archibald

You will need to store the value somewhere (eg in the Tag property of the control) then you call the routine, and once that has run, update the Tag to the new value.

cynx

ASKER

Thanks, was able to achieve this by storing values in temparray