ActiveX control dilemma

[Brett]

I have a "master" ActiveX Combobox on a sheet which I copy & paste anything
up to 30 times for each client. I would like to shift the focus from the
control after it has had a data change (say select the linked cell for that
control). The problem as I see it is that even though I can have a change
macro for the master it would need to be duplicated for each control and
that's not really feasible (using Chip Pearson's code to copy a macro into a
module).

Is there a more generic way that I can set it up to select the control's
linked cell (or any other cell) please?. Brett

 

[joel]

You can have a common routine for all the controls. You need to have a
minimum of 3 lines in each On change macro

Private Sub ListBox1_Click()
call CommonClick
end Sub

You may need to pass a parameter to the common routine depending on what you
are doings

Private Sub ListBox1_Click()
call CommonClick("listbox1")
end Sub

 

[Brett]

Hi Joel, actually each macro would only be that long
PS
Range select
ES
but the problem is creating that macro for each control anyway (there's 90
potential comboboxes, of which up to 30 can be used at any given time, so
they're created on demand).

 

[joel]

You are going to have to have the 30 or 90 mactos. When you create the
control on demand you have to give the control one of the 90 names of the
prediefined macrtos. You dont have to create the macro after the control
exists.

VBA will give the control a name which you can change after to create it on
demand to the control name match the name in the predefined macro.

 

[Brett]

Hi Joel - yes, I thought it may end up as that, but I was trying to avoid
carrying all those macros around in each book (one of them has 3 x 90
potentials!). Thanks, Brett