Collections of control - evaluating the name of a control that has been "pressed"

Hi,

I have created a form in Excel, upon which is a multipage control, with a set of combo boxes placed on. One combobox is populated by a pre-defined list of text, which upon user selection runs a macro that defines the population of a second combo box.......like a choice - sub-choice set up.

The user is able to add and delete pages to their requirements, which also add and deletes the combo boxes on each page, however the user will always be presented with the same choices for each page but at any given time the coices on individual pages may or may not match the choices on others.....at least that's what I want.

I want a common code that operates on ALL combo boxes, however my problem is that because each combobox has a different name they would usually require a separate code behind them.....or at least a piece of code that points to a common one.

Is there a way in which I can get a piece of code to operate such that it will look for changes in ANY combobox regardless of the page it is on (possibly use a control collection ), evaluate the name of the combobox that the user has activated then pass that name onto another macro which will use it to populate the "sub-choice" combobox that is associated with it.

Thanks for any ideas or solutions!

Answer this question

Collections of control - evaluating the name of a control that has been "pressed"

Kenn Roland
Per the support engineer working on this issue...

I was trying to reproduce the problem being faced by the customer and needed the following clarifications:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
1. Where are the combo boxes on multi-page control or on worksheets
2. How are combo boxes created Are they created by user or are they created programmatically
We need answers to these question to understand the issue better and to try and get a resolution.

please post your reply here and we'll make sure that it gets back to the engineer.

thanks,
-brenda (ISV Buddy Team)
Pradeep K
Hi,

In answer to your questions

1. The combo boxes are multi-pages (which themselves are of course on a form)

2. The combo boxes are created programmatically when the user adds another page to the multi-page collection. Similarly the page with the highest page number is deleted if the user decided to delete. Basically I evaluate the number of pages in the multi-page collection, add another page named incrementally after the last, and similarly name the combo boxes with the same number. e.g. multi_page_x will contain combo1_page_x, combo2_page_x etc, where x is the page number.

Thanks for your help!
Deskman
Hi,

Our support engineer has researched this issue with the Excel team and found that it is not possible to have control arrays in Excel. It is also not possible to have single Sub commonly handling events from combo boxes. This is by design as Excel is not intended to be programmed for such purposes. Hence it is not possible to achieve what you are trying to do.
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Sorry I couldn't find better news for you,
-brenda (ISV Buddy Team)

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Collections of control - evaluating the name of a control that has been "pressed"

Answer this question