Having a Drop-down list in Word referring to an Excel list

gmayor · #4 07-11-2016, 11:40 PM

The code is intended to fill a list or combo box on a userform. It is not intended to fill a dropdown list in a document. You can use variations of the same code to fill more than one list or combobox, from the same or different worksheets.

Note that the code inserts the whole worksheet (or range) into the list or combo box, but displays only one selectable column. The other columns are present but hidden, to avoid cluttering the display. If all the information you wish to include in your document is in a single record on the worksheet. The same userform list or combo can be employed to write the selected record to the document, by writing the appropriate column of the box to the relevant part of the document.

I would suggest using bookmarks at those document locations and use the code at the end of the first of my linked pages to write the column values to the bookmarks.

Code:

Private Sub CommandButton1_Click()
    If ComboBox1.ListIndex > 0 Then
        FillBM "BookmarkName1", ComboBox1.Column(1)
        FillBM "BookmarkName2", ComboBox1.Column(2)
        'etc
    End If
    Unload Me
End Sub

The third box contains a the xlFillList function. It is shown as a Public function so it can go either in a new module in the document template, or it can go in the userform code module and the userform can access it from either place.

The combo or list box goes on the userform and the code in the first of the code boxes on the web page goes in the Initialization code of the userform to populate the list or combo box when the userform is called. e.g.

Code:

Private Sub UserForm_Initialize()
'The displayed worksheet column is iColumn - here column 2. Change as required
'Change the workbook path and worksheet name as appropriate
'Change ComboBox1 to the name of the list or combo box on the userform
    xlFillList ListOrComboBox:=ComboBox1, _
               iColumn:=2, _
               strWorkbook:="C:\Path\WorkBookName.xlsx", _
               strRange:="SheetName", _
               RangeIsWorksheet:=True, _
               RangeIncludesHeaderRow:=True
    ComboBox1.ListIndex = -1
End Sub

See http://www.gmayor.com/installing_macro.htm

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to import list from Excel into drop-down list into word	ahw	Word VBA	43	02-28-2020 08:11 PM
Microsoft Excel, Drop Down List	Phibir	Excel	1	04-16-2015 01:01 PM
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data	Faldinio	Word VBA	7	10-19-2014 06:03 AM
(Excel 2007) Need help with colour change when condition met in drop down list	oalrashdan	Excel Programming	1	12-13-2013 02:21 AM
drop down list from external data source Excel 2007?	Intruder	Excel	1	08-03-2012 05:41 AM