ActiveX ComboBox selection writes multiple cells

**darmstrong** · 05-11-2007, 11:05 PM

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

**Leith Ross** · 05-12-2007, 02:17 AM

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

**darmstrong** · 05-12-2007, 04:13 AM

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

**Leith Ross** · 05-12-2007, 02:29 PM

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

**darmstrong** · 05-13-2007, 10:00 AM

Leith,

That's pretty much the functionality I was looking for. I adapted it to my spreadsheet, so now, the combo box on Sheet1 Cell B8 stuffs values into that row (it stuffs the grey text columns, which will be hidden, and then I calculate extended values based on column A quantity).

What I need to do now is have the same functionality in about 20 rows below row 8 - where the exact same drop-down box selections allow a different item to be selected, quantities tabulated. For example, the combobox in B9 need to work exactly like the one in B8, but of course stuffs values into row 9.

Instead of hard-coding the "Combobox" in the subroutine, I need to use variables and have the selection change apply to whichever row the new drop-down selection was made. I guess every combobox has to have a subroutine, right, but I'll need to have that subroutine call the main one, sending it a variable that makes the subroutine act on the row the selection was made in.

The only other things I'll need, are:
- to make the routine that stuffs values into the combobox run automatically when the spreadsheet is opened.
- to make that routine also sense how many rows in Sheet 3 have data, and stuff the combobox with column A values for that number of rows (instead of hard-defining the Range A2:A5 as it is now).

Thanks again for all your help.

David

**Leith Ross** · 05-14-2007, 01:45 AM

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