Graham Mayor

... helping to ease the lives of Microsoft Word users.

Many people access the material from this web site daily. Most just take what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.

Populate a userform combo box from data in a table

Rather than populate a userform combo box (or list box) directly in the macro code, it may be useful to read the data into the combo box from a table. This can be an external table document (or Excel worksheet) but for the purpose of this exercise, the table, the userform and the associated vba code are all stored in a Word template to be used as an add-in.

Create a table

The example uses a simple table in the body of the add-in template. The associated macro code will cater for tables with a different number of columns, should you wish to store additional data in the combo box. The last column of the table is used to store an in-line graphic of the signature associated with the data in the row. Graphics may not be stored in a  combo box, so instead you could call a graphic file to insert the signature, or as here simply re-read the table and insert the signature directly from the table cell.

The table shown in the example below has four columns. The code will accommodate more or fewer columns should you wish to change the data, and to make more room the template can be used in landscape mode and with smaller fonts. The graphics however are inserted exactly as shown in the table.

The userform in the example template that accompanies this page has but one field and is as shown below. Obviously in a real world application the combo box would likely be used alongside other fields.

The macro is run from a button on the ribbon.

... and produces the following dialog:

The combo box, as can be seen from the above illustrations, displays only the values from the first column of the table, the remaining values are stored in zero width columns and are thus invisible - nevertheless the data from those columns is available to be used in association with the selected item.

The macro associated with the userform inserts the signature graphic at the end of the document, then adds the content of the other columns with accompanying descriptive text. The text is then formatted using range formatting commands to produce:

Because I have included the template as a downloadable file, I have not reproduced the code it uses on this page. That code is annotated in the template and can be examined there and modified to your requirements.

- Now download the example form.

Populate a list box or combo box from an Excel worksheet

The above example demonstrates how to fill a list box from a Word table. That method is quite satisfactory for small data sets, but where there are many records to load into a form, the process is slow and cumbersome, and in such circumstances it is better to store the data in an Excel worksheet and read the worksheet directly into the userform.

When working with multi-column list and combo boxes, it can be difficult to keep track of the column assignments. To this end I have prepared a Column Values add-in to help overcome the difficulty.

Using ADO to connect to the Excel file directly, there is no need to open Excel and the process is almost instantaneous. To facilitate this I have developed a small but versatile function, which will fill any userform list or combo box with the contents of a worksheet or a named range in a worksheet.

The code is reproduce below. Copy it to a new vba module and call it from the userform initialisation or from the macro that calls the userform with a command line similar to the following.

To use a named worksheet:

xlFillList ListOrComboBox:=Me.ListBox1, _
iColumn:=2, _
strWorkbook:="C:\Path\WorkBookName.xlsx", _
strRange:="SheetName", _
RangeIsWorksheet:=True, _
RangeIncludesHeaderRow:=True

To use a named range:

xlFillList ListOrComboBox:=Me.ListBox1, _
iColumn:=2, _
strWorkbook:="C:\Path\WorkBookName.xlsx", _
strRange:="RangeName", _
RangeIsWorksheet:=True, _
RangeIncludesHeaderRow:=True

The function fills the list of combo box with the columns from the worksheet/range, but only displays the column (iColumn) at the full width of the box. The remaining columns are set to zero width.

Option Explicit

Public Function xlFillList(ListOrComboBox As Object, _
iColumn As Long, _
strWorkbook As String, _
strRange As String, _
RangeIsWorksheet As Boolean, _
RangeIncludesHeaderRow As Boolean, _
Optional PromptText As String = "[Select Item]")

Dim RS As Object
Dim CN As Object
Dim numrecs As Long, q As Long
Dim strWidth As String

If RangeIsWorksheet = True Then
strRange = strRange & "$]"
Else
strRange = strRange & "]"
End If

Set CN = CreateObject("ADODB.Connection")


If RangeIncludesHeaderRow Then
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Else
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
End If


Set RS = CreateObject("ADODB.Recordset")
RS.CursorLocation = 3

RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 'read the data from the worksheet

With RS
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With

With ListOrComboBox
.ColumnCount = RS.Fields.Count
If RS.RecordCount > 0 Then
.Column = RS.GetRows(numrecs)
End If

strWidth = vbNullString
For q = 1 To .ColumnCount
If q = iColumn Then
If strWidth = vbNullString Then
strWidth = .Width - 4 & " pt"
Else
strWidth = strWidth & .Width - 4 & " pt"
End If
Else
strWidth = strWidth & "0 pt"
End If
If q < .ColumnCount Then
strWidth = strWidth & ";"
End If
Next q
.ColumnWidths = strWidth
If TypeName(ListOrComboBox) = "ComboBox" Then
.AddItem PromptText, 0
If Not iColumn - 1 = 0 Then .Column(iColumn - 1, 0) = PromptText
.ListIndex = 0
End If
End With

'Cleanup
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing

lbl_Exit:
Exit Function
End Function

 

 

 Combo box

This page may be read in association with the simple Userform tutorial

My friend and frequent contributor Greg Maxey has developed his own web page which includes a variety of methods of populating list and combo boxes from external data and makes excellent reading