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