Populating Word 2013 drop down with Excel list

mdkusername

New Member
Joined
Dec 9, 2015
Messages
31
I am trying to use a list from Excel as the source for a combo box in Word 2013. Is there any to do this?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try the following code, which needs to be placed in a regular module (Visual Basic Editor >> Insert >> Module) within your Word document. Note that I've included comments in the code, so make the necessary changes where indicated.

Code:
'Force the explicit declaration of variables
Option Explicit


'The following procedure will add a list of items from the specified
'range in a worksheet of an Excel workbook to the specified combobox
'in this document (the one running this code)
Sub AddItemsToContentControl()
    
    'start a new instance of Excel
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    'xlApp.Visible = True
    
    'open the specified workbook (change the path and filename accordingly)
    Dim xlSourceWorkbook As Object
    Set xlSourceWorkbook = xlApp.workbooks.Open( _
        FileName:="c:\users\domenic\desktop\sample.xlsm", _
        ReadOnly:=False)
    
    'set the range containing the list of items
    Dim xlSourceRange As Object
    With xlSourceWorkbook.worksheets("Sheet1")
        Set xlSourceRange = .Range("A2:A" & .Cells(.Rows.Count, "A").End(-4162).Row) '-4162 = xlUp
    End With
    
    'get the content control having the title "MyTitle" and tag "MyTag" (change accordingly)
    Dim oContentControl As ContentControl
    Dim itemsAdded As Boolean
    Set oContentControl = GetContentControl("MyTitle", "MyTag")
    If Not oContentControl Is Nothing Then
        'add the entries from the range to the content control
        AddDropdownListEntries oContentControl, xlSourceRange
        itemsAdded = True
    End If
    
    'close the Excel workbook
    xlSourceWorkbook.Close SaveChanges:=False
    
    'quit the Excel application
    xlApp.Quit
    
    'display message to user of success or failure
    If itemsAdded Then
        MsgBox "Items added to ComboBox.", vbInformation
    Else
        MsgBox "Content control not found!", vbExclamation
    End If
    
    'clear the objects from memory
    Set xlApp = Nothing
    Set xlSourceWorkbook = Nothing
    Set xlSourceRange = Nothing
    Set oContentControl = Nothing
    
End Sub


'This function returns the content control having the title theTitle and the tag theTag
Public Function GetContentControl(ByVal theTitle As String, ByVal theTag As String) As ContentControl


    Dim oContentControl As ContentControl
    
    For Each oContentControl In ThisDocument.ContentControls
        If oContentControl.Title = theTitle And oContentControl.Tag = theTag Then
            Set GetContentControl = oContentControl
            Exit Function
        End If
    Next oContentControl
    
    Set GetContentControl = Nothing
    
End Function


'This procedure adds the items from the range xlSourceRange to theContentControl
Public Sub AddDropdownListEntries(ByVal theContentControl As ContentControl, ByVal xlSourceRange As Object)


    theContentControl.DropdownListEntries.Clear
    
    Dim xlCell As Object
    For Each xlCell In xlSourceRange
        theContentControl.DropdownListEntries.Add Text:=xlCell.Value
    Next xlCell
    
End Sub

Hope this helps!
 
Last edited:
Upvote 0
I have upload a folder to OneDrive, where you can download two files. One file is the Word document that contains both the combobox and macro to replace the items in the combobox. The other is an Excel workbook that contains the list of items to replace the existing ones.

Simply download these two files, and make sure they're located in the same folder. Then, open the Word document, and run the existing macro. You'll see that the items in the combobox have been replaced with the new items from the Excel workbook.

Let me know if you have any questions. Here's the link...

https://1drv.ms/u/s!Atmin3kqXqL0gjUh25VXrCw8bEsS?e=bXt7X3
 
Upvote 0
Thank you for the help, I am still unable to get it to work. I have a Word for office 365 document saved as: carotid test; with three combo boxes titled: carotid artery disease; symptoms (ROS); signs (PE)
Then I have an Excel saved as carotid test; on sheet 1 I have 3 columns named: Carotid_artery_disease; Symptoms_ROS, Signs_PE

Using the code you kindly provided how would I change it to for the documents I described?

I tried you more recent post bu I am unable to download a macro containing document.
 
Upvote 0
Thank you for the help, I am still unable to get it to work. I have a Word for office 365 document saved as: carotid test; with three combo boxes titled: carotid artery disease; symptoms (ROS); signs (PE)
Then I have an Excel saved as carotid test; on sheet 1 I have 3 columns named: Carotid_artery_disease; Symptoms_ROS, Signs_PE

Using the code you kindly provided how would I change it to for the documents I described?

I tried you more recent post but I am unable to download a macro containing document.
 
Upvote 0
When you say that you are unable to get it to work, what do you mean exactly. Do you get an error? If so, which one and on which line? Also, did you make the necessary changes, where indicated in the code?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top