ActiveX ComboBox selection writes multiple cells

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