Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX control dilemma
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX control dilemma
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX control dilemma
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX control dilemma
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX control dilemma
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 "joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
publish activeX combobox and other activeX control | Excel Programming | |||
Help with using an activex control | Excel Programming | |||
How to control "Date Time Picker ActiveX Control" | Excel Programming |