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 reproduced at the end of this page. 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:
iColumn:=2, _
strWorkbook:="C:\Path\WorkBookName.xlsx", _
strRange:="SheetName", _
RangeIsWorksheet:=True, _
RangeIncludesHeaderRow:=True
To use a named range:
In the example below the
RangeIncludesHeaderRow is shown as False. If the range does
include a header row, change this to True.
iColumn refers to the column in the range, not the column in the
worksheet from which the range is a subset.
iColumn:=1, _
strWorkbook:="C:\Path\WorkBookName.xlsx", _
strRange:="RangeName", _
RangeIsWorksheet:=False, _
RangeIncludesHeaderRow:=False
The xlFillList Function
The function fills the list or 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.
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]")
'Graham Mayor - https://www.gmayor.com - Last updated - 20 Sep 2018
'ListOrComboBox is the name of the list or combo box to be filled
'iColumn is the column in the sheet (or the range) to be displayed in the list/combo box
'strWorkbook is the full path of the workbook containing the data
'strRange is the name of the worksheet or named range containing the data
'RangeIsWorksheet - set to True if the range 'strRange' is a worksheet
' or False if it is a named range
'RangeIncludesHeaderRow - Set to True is the Worksheet or named range contains a header row
'PromptText - Use a text string here to add your preferred prompt text to a combobox.
'The PromptText is not used for ListBoxes.
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=YES;IMEX=1"""
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
Please note that any download links placed by advertisers on this page do not relate to the contents of the page.
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