Populate Combo/List Box From Cells on Worksheet

RJ

You seem to believe that the built-in functionality in Excel does/would not enable you to "change the data in the list easily." How come, assuming that I'm reading you right?

Aladin

If I use the additem method with a list box or combo box, I only know how to hard code the items. I would like to be able to place the items for the list box on a sheet where the user can change the items easily and it also changes in the list box on the user form.

If the number of Items in your list box vary, the easiest way is to set the list as a Range Name and refer to that. This method can also be used for non variable length lists but the advantage of using Range Names is that the range can be altered from the worksheet end.

Alternately: Run a scanning while loop in the form initialisation to find out how many entries and set parameters appropriately.

hope this helps

Rob.

RJ

Just put the (initial) items as you propose on a separate sheet, say, in column A from A2 on (use A1 for an intelligible, descriptive label).

Activate A2 on this sheet.
Activate the option Insert|Name|Define.
Enter a name, say, SLIST (from Source List) as Names in Workbook.
Enter for Refers to:

=OFFSET(x!$A$2,0,0,COUNTIF($A:$A,"*?*)-1,1),

where x is the name of the current sheet.

Activate the cell where you want to have the list box/dropdown list.
Activate the option Data|Validation.
On Data Validation dialog, select List for Allow.
Enter as Source:

=SLIST

The users may add to or delete from the list of items on the separate sheet anything they want. List box will change accordingly.

Hope I interpreted you right.

Aladin

Rob,

A named dynamic range would work better in this situation than an ordinary named range.

Aladin