ActiveX ComboBox selection writes multiple cells

I probably need to buy a book that is a good excel programming reference, but in the meantime, can someone provide me with example code (or better yet, example spreadsheet with code), that shows an ActiveX combobox with at least 2 selections, where say the first selection writes "bob" and "mary" to cells B5/C5, and the second selection writes "dog" and "cat".

Once I see an example, I should be able to figure out the mechanics. I'm surprised that after a bunch of surfing, I wasn't able to find something like this anywhere.

Thanks,

David

Hello David,

The best method is using a demo workbook. I have attached one here. There are 2 combo boxes (Control Toolbox a.k.a. ActiveX) and 2 command buttons. There is one command button for each combo box. The buttons load the combo boxes in different ways. List 1 is loaded using VBA code, and List 2 is loaded using a Range on the worksheet. The combo boxes each use 2 columns to store the data. I was sure if this is what you wanted or if you want store the entry as a single string separated by space e.g. "Bob Mary". The 2 columns seemed more likely.

Sincerely,
Leith Ross

Wow, thanks for the prompt reply. I spent hours digging elsewhere, and didn't get this far!

This gives me a great starting point. What I'm trying to do is slightly different from this though; if you have a few moments, I'd really appreciate a demo of this.

Say another worksheet in this workbook has the following 4 x 3 cell block:

A 1 2 3
B 4 5 6
C 7 8 9

- how do I populate the combo box drop-down selections with values from the 1st column range (A,B,C)? - ie. such that as soon as you open the workbook, these values are automatically already available as drop-down selections?

- when one of the selections in the drop-down is chosen (A, B, or C), how do I fill the next three cells to the right of the combo box with values taken from the next three cells in the same row that the drop-down box came from? So if A is selected, the three cells to the right of the combo box would be filled with 1,2,3; if B selected, 4,5,6, etc.


Given that all this is working, can the worksheet on which the 4 x 3 matrix is defined be hidden, so no one can see where these values are coming from?


Thanks,

David

Hello David,

I made the changes to List 2. The data is on "Sheet3" which is hidden. Only the letters are shown in the ComboBox. When the user makes a selection the data is copied from the hidden sheet to the 3 cells to the right of the ComboBox. If you have any questions, just ask.

Sincerely,
Leith Ross

Hello David,

I have added the functions you wanted to the attached workbook. The row is determined by the cell the ComboBox occupies, so you don't need to pass any variables. Just be careful of future placements. You will have to enter a single line of code for each ComboBox's Click() event. You will see the code on Sheet1. Everything else is automated.

Sincerely,
Leith Ross

Leith,

The structure you set me up with is working very well (Thanks again). My challenge now, is that I want to be able to make selections in the various combo boxes in the Product column (ModelRequirementBreakdown2a.xls), not only by using the combo box dropdown and select mechanism, but also, if possible, by pasting a cell range whose data matches that in the combobox definition.

In other words, if I have a single column cell range from somewhere with the following data in 2 rows:
VNXES
HDX

I want to be able to paste that range starting at B8, such that it makes the combobox selections in B8 and B9, and thereby populates rows 8 & 9 with the appropriate data.

Is this do-able?



I saw an example of another approach to data selection which I liked somewhat, but I don't think it will work. I thought I was good at figuring out things, but I can't crack this simple one.

In the attached spreadsheet, cell A1 of Initial Quality worksheet has a drop down that apparently references the Namelist? "FieldeList" which is on the List worksheet, cells A1-A14.

By examining A1 of Initial Quality, how can you determine that this reference exists?

How do you establish such references to Namelists, with the drop-down arrows?

I like this method of using drop-down lists better than ActiveX combo boxes, because once you've chosen what you want, the appearance is cleaner. Big problem however, is if you try to paste directly into the A1 cell, it doesn't select the item out of the namelist, it overwrites the cell, erasing the namelist functionality. Am I missing something?

Thanks,

David

Leith,

Well, I finally did figure out how to use paste to make a combo box selection - the LinkedCell property of the combobox. I'm getting one odd behaviour though from the LoadComboBox macro you provided.

Because I'm using paste into the LinkedCell under the combobox, it opens the possibility that a value not in the combobox listing may occur. When this occurs, for some reason, the LoadComboBox macro running time for that combobox (when opening the workbook) takes significantly longer to run (maybe 5-10 times). This is adding up, because I'm going to have maybe 50 repeats of the combobox, and there are 6000 items in the list (pushing a minute to open the workbook now)

One other related behaviour, is that with Sheet1 filled out, when the workbook is opened, the already-selected data in the comboboxes is retained as expected when the combobox selections are loaded. For some reason though, if I trigger the LoadAllComboBoxes macro with a button after its open (for testing), the action of loading the comboboxes clears their current selection, except for lines that have data in the cells not found in the combobox list (the same cells taking longer to load the combobox). This isn't in and of itself an issue, but it may be a clue to the problem.

If you have any ideas, it'd be great to hear them. This performance issue is one of the last big hurdles I have before putting this spreadsheet into use.

Thanks again for your help.

David