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.
Userform multicolumn list and combo box column assignments
I frequently prepare automated document templates for private clients, using list and combo boxes, with hidden multiple columns, populated from Excel and Access tables. As each client has different data to display, remembering the data assignment in the hidden columns can be a problem. I therefore developed this add-in for my own personal use to extract the column assignments, but thought that others who work with multi-column list and combo boxes in userforms might also find it useful.
The add-in comprises a simple template compatible with 32 bit versions of Microsoft Office 2007 and later. It has not be tested for compatiblity with the 64 bit versions of Office, but it should be.
The template assumes that the list of combo box would be populated from an Excel worksheet or an Access table (or query). The requirements (and column assignments) for both differ from one another so there are two separate processes selected from an initial userform, selected from a button on the Add-ins tab of the ribbon.
The last used data sources and settings are stored in document variables in the template itself, so the startup folder used needs to have write access by the user. VBA developers using the add-in would almost certainly have that access.
When programming to write values from the various columns of the list or combo box to the document, you might call the columns by number e.g. in the following example by running a function to populate a bookmark in the document with the value of the appropriate column.
With Userform1.ComboBox1
FillBM "bkName", .Column(2)
FillBM "bkDD", .Column(4)
FillBM "bkEmail", .Column(5)
End With
The add-in creates constants (Public or Private) that assign the field names to the numeric values. The constants are copied to the clipboard and optionally produced as a document with the values of the selected record ....
Public Const lng_ID As Long = 0
Public Const lng_Lookup As Long = 1
Public Const lng_Name As Long = 2
Public Const lng_DirectFax As Long = 3
Public Const lng_DirectDial As Long = 4
Public Const lng_EMail As Long = 5
Public Const lng_Closing As Long = 6
Public Const lng_Initials As Long = 7
Public Const lng_Office As Long = 8
Public Const lng_Signature As Long = 9
....which are then rather more meaningful when later evaluating the code thus:
With Userform1.ComboBox1
FillBM "bkName", .Column(lng_Name)
FillBM "bkDD", .Column(lng_DirectDial)
FillBM "bkEmail", .Column(lng_EMail)
End With
The main dialog has context sensitive features, but is simple enough to navigate and self explanatory:
-
Click here to download the add-in
Overview
When programming with multi-column list and combo boxes it is often difficult to recall column assignments when later re-editing the code. This add-in aims to help overcome that, by producing listings associated with the data used to fill the boxes.