Dynamic Combo Box List

A combo box on a form can pick up the list contents from its RowSource property. This can be the name of a worksheet range – such as “products” (not ever a literal address, please).

However, this is tricky if the size of the list changes (if the list grows, specifically). With a standard defined Name, you need to keep on redefining the Name. Alternatively, you can use the OFFSET-function trick for dynamic ranges (but that is obscure and depends on things like the number of non-data cells in the column).

In 2007 we have the Structured Reference naming into Tables, such as “ProductTable[Product]“, which returns us the data contents of the product column. On a recent course, I was asked if we could use this as a Combo box RowSource property. The good news is that we can:

Any additions to the Table are picked up when the form reopens:

You could get this to work for a multi-column list (e.g. id + name, with the id as BoundColumn). For a 2-column table, you can specify the whole data area: “ProductTable[#data]“. It doesn’t matter if the #data area has more columns than the list needs: the leftmost ones are used.

Initially, I thought I’d try this with an ActiveX combo box dropped directly on to the worksheet. However, such on-sheet controls are actually OLEObjects. Rather than a RowSource property, they have a ListFillRange. An OLEObject could be things other than a List/Combo box, in which case this property would be inapplicable. (Similarly, RowSource is actually defined on the Control class).

That’s okay, but unfortunately, OLEObject.ListFillRange does not understand structured references into tables: it needs either a regular Defined Name or (don’t) a literal address. This seems like an oversight: the OLEObject‘s Parent is the Worksheet, which holds the Table as a ListObject – so it should be possible to resolve the name.

Nevertheless, it works fine for the Form combo boxes, and I’ll try to use this technique in the future.

About these ads

5 Responses to “Dynamic Combo Box List”


  1. 1 Jon Peltier March 3, 2010 at 7:03 pm

    I’ve always hesitated to use the RowSource property of a listbox or combobox to fill the list. Using it seems to bind the worksheet and the control too tightly together.

    I’ve always preferred to define a range (named range or something in the code), load the range into an array, process as required, then load the list. In reverse, the list is loaded into an array, processed, and dumped into the sheet.

    Is it just me who feels this way? Is my OCD more ingrained than I’ll admit?

  2. 2 Dick Kusleika March 3, 2010 at 7:58 pm

    I stopped using RowSource and LinkedCell too. I can’t remember why I stopped, but it recently came up on stackoverflow

    http://stackoverflow.com/questions/2330997/bugs-in-excels-activex-combo-boxes/2335046#2335046

  3. 3 Roy MacLean March 8, 2010 at 11:33 am

    Jon: Your approach sounds like a lot of work unless you are modifying the Range contents before populating the list (for example, reducing it to unique values). I think it’s rather clearer to have the Range-dependency visible as a Control property, particularly with the Table reference, rather than having the reference in code. Is it not likely that the form with the ComboBox is functionally bound to the worksheet structure anyway?

    Dick: The reported problems seem to be with the OLEObject version of the Control, not the UserForm version. Presumably this is something weird to do with the OLE wrapper. Personally, I’d probably use only CommandButtons directly on a worksheet. As someone noted, you can always use Data Validation for an on-sheet drop-down list.

  4. 4 Jon Peltier March 8, 2010 at 12:01 pm

    Dick –

    I know conventional wisdom is that the OLE controls are less reliable than the Forms menu controls, but I haven’t really had any trouble with them.

    Roy –

    It’s really not a whole lot of work. I agree the RowSource approach is easier if you aren’t doing anything else with the data. But 90% of the time, the data is being somehow processed, or you’re going to have to exercise tighter control over it. It just makes sense not to start doing it one way if you’re likely going to have to switch to another way.

  5. 5 Ruud March 30, 2010 at 3:24 pm

    Hey all,

    For ActiveX comboboxes Named Ranges can indeed be used.

    And Named Ranges can point to a certain (part of a) Table with a structured reference.
    I have a Table ‘Inv’ with several columns ‘InvID’, ‘InvName’, ‘Pmpp’, etc. (it’s about inverters: equipment needed to feed photovoltaic-generated current into the grid, in case you were wondering ;)), and have defined a Named Range ‘inverter’ as ‘=Inv[[InvID]:[InvName]]‘. This works perfectly, the Named Range always points to these first 2 columns even if rows are added or deleted.

    You (or I, at least) would say that if I use that Named Range ‘inverter’ then as the ListFillRange property of the activeX combobox, it’d also be kept in sync with the actual table.
    Unfortunately not so – if I add a row to the table it does not appear in the combobox list. If I delete a row from the table, in the combobox list it is replaced with a random row from the table.

    Does any one have comments on this, or a workaround?

    Thanks!

    RUUD


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




March 2010
M T W T F S S
« Feb   Apr »
1234567
891011121314
15161718192021
22232425262728
293031  

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: