Using Listboxes

Using a listbox on a form is a common way of outputting data that has been read in, either from a worksheet or from a database. Listboxes also allow users to select an item from the list and that item can then be analyzed or modified.

Here are some tips:

• Clear the listbox before adding data, or provide a button for doing so.
• Set the Font of the listbox to a monospace font such as Courier New during design time.
• Have the first item selected by default.
• Use multicolumn listboxes when necessary.
• Use column headings if you have multicolumn listboxes.

Clear the Listbox Before Adding New Data

When you use the AddItems property of the listbox, it will always append to the data that is already there. You may want to either:

• clear the listbox before the new data is entered
• place a button on your form that will clear the listbox when needed

In some cases, doing both may be useful:

To clear the listbox:

lstOutput.Clear

Use a Monospaced Font

If you are using multiple columns of data in a single column listbox, or even in general, it may be a good idea to change the font of the listbox to be a monospaced font such as Courier New, instead of a proportional font like Tahoma. This will allow the data to line up better. Placing data in a multicolumn list box will keep the data aligned, but using a monospaced font might still improve the results:

The box on the left uses Courier New, a monospaced font. The box on the right uses a proportional font.

Here is a more complex example. First is the monospaced or fixed width font, then is the proportional font. Some of the alignment in this example is done using the vbTab constant which places a tab character:

lstBills.AddItem "Item ID" & vbTab & "Description" & " " & vbTab & " Qty "

Use Multicolumn Listboxes When Necessary

A lot of the problems you could run into when aligning data in columns can be avoided if you use multicolumn listboxes.

To have multiple columns in your listbox, you first have to set the ColumnCount property of the listbox to be the number of columns you want, either using code or at design time:

lstOutput.ColumnCount = numFields

[ add more stuff here ]

Using Column Headings

Turn on column headings:

lstOutput.ColumnHeads = True

Note: In VBA, it is not possible to add column headings to the listbox unless the data is first stored in a worksheet, and the RowSource property is set to that range. Hey! Why don't you try that....

Examine the example called A Product Mix Application (Product Mix.xlsm) to see great use of the listbox in a real application.

See listbox example.xlsm for a general demonstration.