<

How to dynamically control form List Box controls in Excel.

Published on
32,700 Points
21,600 Views
6 Endorsements
Last Modified:
Awarded
Community Pick
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

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:

Cool List Box Controls
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

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!

Sample Project with a Dynamic Chart
Enjoy!  Sample-ListBox-Control.xls

~Alain Bryden
6
Comment
Author:alainbryden
  • 2
2 Comments
 

Expert Comment

by:sydneyguy
have been going through your sheet and have a question and have posted this question up at http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26453203.html
if you could lend a hand would be most apprecated
thanks
0
 

Expert Comment

by:sydneyguy
by the way love your coding very nice looking and professionally presented
0

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month