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.


7 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


  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?



  6. 6 photovoltaic Definition June 4, 2013 at 10:32 pm

    At present, Welspun Energy renewable jobs australia has about 30 MW
    installed solar project and expects to add another 80 MW capacity by the end of the first quarter comes.
    We did have a very good result from our southeastern

  7. 7 Lavelle Sappington March 15, 2016 at 12:13 pm

    Thank you for perhaps the most level headed thing I have read today. I think it could also be useful for everyone to know how and where to fill a form online. BTW, if anyone needs to fill out a NYC RPIE-2010 Instruction, I found a blank fillable form here http://goo.gl/XryzZL

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

March 2010
« Feb   Apr »

%d bloggers like this: