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