Method 1 – Using the Paste Special Command
Steps:
- Select the cell that contains the drop-down list in the Excel file. In this case, it will be B4.
- Press Ctrl+C to copy it.
- Open the Word file.
- Go to the Home tab.
- Select the Paste option.
- Click on the Paste Special command.
- A prompt will show up on the screen.
- In the prompt, first, select the Paste Link oval.
- Sselect the Microsoft Excel Worksheet Object under As.
- Click OK.
- Change the list contained in the drop-down list in the Excel file.
- The content in the Word file will be changed accordingly.
Read More: How to Put a Large Excel Table into Word
Method 2 – Applying VBA Code
Steps:
- Create a list in the Excel file that you want to add as a drop-down list in the Word file.
- Open the Word file.
- Go to the Developer tab.
- From the Controls group, select the insert Combo Box Content Control command.
- We will have a drop-down box on the screen.
- Select the Developer tab.
- Choose the Visual Basic command.
- A prompt will open.
- In the Visual Basic window, choose the Tools option.
- From the drop-down list, choose the References option.
- In the References dialog box, choose the Microsoft Excel 16.0 Object Library option under Available References.
- Click OK.
- Go to the Insert tab.
- Select the Module option.
- In the opened module, enter the following code and save it.
Sub DropDownListFromExcel()
Dim exlApp As New Excel.Application, xlWrkBok As Excel.Workbook
Dim wkbkName As String, sheetName As String, LRow As Long, a As Long
Application.ScreenUpdating = False
wkbkName = "C:\Users\user\Desktop\Adnan\Drop-Down-List-in-Word-from-Excel.xlsx"
sheetName = "VBA Code"
If Dir(wkbkName) = "" Then
MsgBox "The mentioned Workbook is not found." & wkbkName, vbExclamation
Exit Sub
End If
With exlApp
.Visible = False
Set xlWrkBok = .Workbooks.Open(FileName:=wkbkName, ReadOnly:=True, AddToMRU:=False)
With xlWrkBok
With .Worksheets(sheetName)
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
Selection.Range.ContentControls(1).DropdownListEntries.Clear
For a = 5 To LRow
Selection.Range.ContentControls(1).DropdownListEntries.Add _
Text:=Trim(.Range("B" & a))
Next
End With
.Close Savechanges:=False
End With
.Quit
End With
Set xlWrkBok = Nothing: Set exlApp = Nothing
Application.ScreenUpdating = True
End Sub
- Run the code by clicking the triangle-shaped green button.
- We will see that a drop-down list is added to the Word file from the Excel file.
How to Create Drop-Down List in Excel
Steps:
- Select the cell where you want to add the drop-down list. In this case, the cell will be D5.
- Go to the Data tab.
- Choose the DataTools group.
- From the Data Validation option, select the Data Validation command.
- In the Data Validation dialog box, choose List under the Allow option.
- Select the range of data that you want to add to that list under the Source option. In our case, the range will be B5:B10.
- Click OK.
- We will have a drop-down list in our desired cell.
Download the Practice Workbook
Related Articles
- How to Insert Excel Table into Word
- How to Copy Table from Excel to Word
- How to Paste Excel Table into Word Fit to Page
- How to Copy Excel Table to Word with Gridlines
- How to Paste Excel Table into Word in Landscape
- How to Auto Populate Word Document from Excel
- How to Create Multiple Word Documents from Excel
- How to Insert Excel Table into Word with Formulas
<< Go Back to Export Excel to Word | Export Data from Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!