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

[Tom Ogilvy]

You can adapt this approach to do it.

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

--
Regards,
Tom Ogilvy



"cmpcwil2" wrote:

>
> 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?
>
>
> --
> cmpcwil2
> ------------------------------------------------------------------------
> cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411
> View this thread: http://www.excelforum.com/showthread...hreadid=538838
>
>

[cmpcwil2]

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