Advanced controls in userforms with VBA macros
Part four of a nine-part series of blogs

The two previous parts of this mini-blog have shown how to draw forms and how to write code to handle form events. This final part shows how to add some of the more exotic controls to user forms, like combo boxes, list boxes, multipage controls, spinners and option buttons.

  1. Advanced Controls - Our Example
  2. Multipage Controls
  3. Combo boxes (and list boxes)
  4. Multiple Column Combo Boxes and Listboxes (this blog)
  5. Multi-select ListBoxes
  6. SpinButtons (Spinners)
  7. Check Boxes
  8. Option Buttons (Radio Buttons)
  9. The Calendar Control

This blog is part of our Excel VBA tutorial.  Wise Owl's main business is running Excel, VBA and other courses for businesses.

Posted by Andy Brown on 28 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Multiple Column Combo Boxes and Listboxes

Why Use a Multi-Column List

It can sometimes be useful to display one thing in a combo or list box, but store another.  Here's a case study where we want to get the id field of the person ordering a drink, but display the name:

List of person names and ids

We have a list of records from a database query or view, and want to allow a user to choose a person.

 

We want to be able to choose by name, but store by number:

Drop list of people Worksheet showing person id chosen
You choose the person ... ... but store the name

Combobox or Listbox Properties for Multiple Columns

To get our example above to work you need to set some properties of the combo or list box:

Properties of combo box

We'll need to set the following properties:

 

On a rainy afternoon, you could try playing about with the ColumnHeads, ListRows and ListStyle properties to see how they affect how the combo or list box looks.

Writing Code to Populate the Combo or List Box

Having created a combo (or list) box and set its main properties as above, you can now use ADO code to get the underlying records and add them into the list of possible values.  Here's an example:

Private Sub UserForm_Initialize()

'=============================

'ADO code to get at data

'=============================

'create a new connection string

Dim cn As New ADODB.Connection

'say where the connection string is pointing to, and open connection

cn.ConnectionString = "driver={SQL Server};" & _

"Server=ANDYB\SQL2008R2;Database=Movies;Trusted_Connection=True;"

cn.Open

'create a new recordset

Dim rs As New ADODB.Recordset

'open a recordset of table of people

rs.Open "vwPerson", cn

'=============================

'populate values in combo box

'=============================

'first clear any existing people's names

cmbPerson.Clear

'add in all of the people one by one

Dim NumPeople As Integer

NumPeople = 0

Do Until rs.EOF

'for each record in table, increment number of people

NumPeople = NumPeople + 1

'add a new item to list

cmbPerson.AddItem

'set the value for first column to be person's name

cmbPerson.List(NumPeople - 1, 0) = rs("PersonName")

'set the value for second (hidden) columb to be person's id

cmbPerson.List(NumPeople - 1, 1) = rs("PersonId")

'go on to the next person

rs.MoveNext

Loop

rs.Close

cn.Close

End Sub

Notice that you add each item to the list, and then set the values for the item's first and second columns (ie the person's name and id). 

 

This blog has 0 threads Add post