This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 blog) 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.

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 blog)
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.

Posted by Andy Brown on 28 February 2012

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:

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).

Parts of this blog
Advanced Controls - Our Example Multipage Controls Combo boxes (and list boxes) Multiple Column Combo Boxes and Listboxes (this blog) Multi-select ListBoxes SpinButtons (Spinners) Check Boxes Option Buttons (Radio Buttons) The Calendar Control

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Multiple Column Combo Boxes and Listboxes

Why Use a Multi-Column List

Combobox or Listbox Properties for Multiple Columns

Writing Code to Populate the Combo or List Box

Head office

London

Manchester