Change event for comboBox

[cmpcwil2]

Hi,
I have multiple VB toolbox comboBox's on a Excel spreadsheet, when a
selection change occurs it makes a call to a webservice to populate
adjacent cells with the returned data.

At the moment I have simply copied and pasted the code into each change
event for each ComboBox, is there a way to have a single module that can
detect which box made the change and then make the call?

A remember from doing some VB years ago that this was possible on forms
using the index number I think, but not been able to replicate this on
an Excel spreadsheet.

Can anyone help?

 

[rmullen]

hm - if i understand your question, i think you're asking about a 'god
function' you can call to populate the box ..

what you'll want to do is create a new class module (right click on
modules, and then pick class module). and then put your code there in a
function. for each place you want to populate the drop down box - just
"call myFunction"

Public Function myFunction
<put your code here>
End Function

 

[Ivan Raiminius]

Hi,

you need a class module that will catch the events. You have to connect
(instantiate) each combobox to this class module (preferably store
these connections in a collection).

See help for application events - not exactly what you need, but will
point you in right direction.

You class module should from the start look like:

Option Explicit

Public WithEvents evtCombo As ComboBox

Let me know if you need more help.

Regards,
Ivan

 

[Guest]

You can adapt this approach to do it.

http://www.j-walk.com/ss/excel/tips/tip44.htm

 

[cmpcwil2]

Thanks for your help I will take a look and give it a go, really do
appreciate it