- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
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.
- Advanced Controls - Our Example
- Multipage Controls
- Combo boxes (and list boxes)
- Multiple Column Combo Boxes and Listboxes (this article)
- Multi-select ListBoxes
- SpinButtons (Spinners)
- Check Boxes
- Option Buttons (Radio Buttons)
- 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.
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:
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:
|
|
| 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:
We'll need to set the following properties:
- The ColumnCount is 2, because there are two columns: the one shown, and the one whose value is stored.
- The BoundColumn is 2, because it's column 2 whose value we are storing.
- The ColumnWidths shows that the first column will be visible, but the second one won't be (it'll have zero width).
- The ControlSource shows that the value chosen will be stored in the range called PersonId on the worksheet.
- The RowSource should be left blank, as we'll populate the items in the combo box dynamically.
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).
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.
- Advanced Controls - Our Example
- Multipage Controls
- Combo boxes (and list boxes)
- Multiple Column Combo Boxes and Listboxes (this article)
- Multi-select ListBoxes
- SpinButtons (Spinners)
- Check Boxes
- Option Buttons (Radio Buttons)
- 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.
Comments on this blog
This blog has 3 comments: