How to dynamically control form List Box controls in Excel.

What is a Form List Box? (skip if you know this)
The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab in the Ribbon" (Check it). In excel 2003, just view the controls toolbar. When you go to add a control from the developer tab (Insert button) or control toolbar, you see Form Controls on top and ActiveX controls on bottom. If you've ever played around with these, you'd know theres a pretty big difference in what you can and can't do with each.


Why use a Form Control over an ActiveX control?
You're torn. When creating controls in Excel, ActiveX controls are so much easier to programmatically manipulate and format, but damn are they ugly, and they behave in such unexpected ways.

Instead of inserting an ActiveX control, you can insert a corresponding Forms control, which is much more refined, and much prettier, but is hardly customizable.

The ListBox control is a good example of one where you might prefer the Forms control over the ActiveX equivalent, but there are all sorts of snags when dealing with it. If you don't mind a little techno-babble, one example is that the collection which contains ListBoxes is hidden, not in the Object Model, but without it, you're forced to use the read-only Shapes collection, for which the only way to change the list contents is to associate the list with a range (only useful in very specific cases) or to use the AddItem and RemoveItem functions, which are limited.

In VBA, it's a bit of a challenge finding your listBox. You can't just say myListBox.property. You have to find it in the Worksheet.ListBoxes collection, which contains a reference to every listBox in your sheet. You can name your listbox in the same way you name a range, by selecting it any replacing the name in the top left hand corner in Excel. This same name will be the one you use to find it in VBA. You find it using Worksheets(Sheet1).ListBoxes(My Control). Once you've found the ListBox object in the appropriate Worksheet.ListBoxes() item, you're well on your way to programatically dealing with it.

Access your forms List Boxes with:
Worksheets("Sheet1").ListBoxes("List Box 1")

You can add and remove items at will using the member functions, but some other desirable tasks are a bit more of a challenge.


Working with a List Box
So how are you supposed to edit an item or change the order? Fortunately for you, I insisted on getting Excel to behave the way we wish it would, and came up with some simple routines for re-ordering items at the click of a button in a Forms List Box Control, so now you can too. Variations of these will let you do things like remove an item, edit it, and reinsert it without the user noticing the change:

'Changes the order of items in a column list. Shifts the selected item up.'
                      Private Sub ColumnIndexUpParamed(ByRef listBoxColumns As ListBox)
                          Dim i As Integer
                          Dim temp As Variant
                          With listBoxColumns
                              i = .ListIndex
                              If i > 1 Then
                                  .Selected(i) = False
                                  temp = .List(i - 1)
                                  .List(i - 1) = .List(i)
                                  .List(i) = temp
                                  .Selected(i - 1) = True
                              End If
                          End With
                      End Sub
                      

Select all Open in new window

By using dynamic routines like this, you can recycle it and use it for all similar controls making your code easier to read, more compact, and easier to make small changes too. My use for this is a simple list box that is populated from a drop down list, and resorted using a couple of buttons:


This is a favourite control of mine, and very customizable, as the pieces are built individually. Buttons are simply hooked up to the appropriate methods.

The following code demonstrates how I used the DropDown lists (which are populated with column names from another sheet) and the "+" (Add) button to add new items to the List Boxes of selected Columns.

 

Private Sub AddColumnParamed(ByRef listBoxColumns As ListBox, ByRef dropDownColumns As ControlFormat)
                          Dim valueExists As Boolean: valueExists = False
                          Dim i As Variant, valueToAdd As String
                          valueToAdd = dropDownColumns.List(dropDownColumns.Value)
                          If Not listBoxColumns.ListCount = 0 Then
                              For Each i In listBoxColumns.List
                                  If i = valueToAdd Then valueExists = True: Exit For
                              Next i
                          End If
                          If Not valueExists Then listBoxColumns.AddItem (valueToAdd)
                      End Sub 
                      Private Sub RemoveColumnParamed(ByRef listBoxColumns As ListBox)
                          If listBoxColumns.ListIndex <> 0 Then _
                              listBoxColumns.RemoveItem (listBoxColumns.ListIndex)
                      End Sub
                      

Select all Open in new window

Here lbPlotValues is the name I've given my List Box of selected values to plot. ddPlotValues is the name of the drop down list of plot-able values.

This looks like fun, maybe even useful but...
I know there's a lot going on here, and it might be difficult to reproduce the control, especially since you would have to draw and align all those buttons and controls so perfectly, so I've put together a sample file where you can play around with it, and copy the code and controls to various applications you might think of. It's great when you're looking for a way to let the user control what values are plotted in a chart or something.

In this example, there is a big table with many rows and columns, and two list boxes. The user can add columns and rows to each, and it determines what appears in the graph below. Neat!


Enjoy!   Sample-ListBox-Control.xls

~Alain Bryden