Use Word VBA to set Content Control from Excel Spreadsheet

Can you post the code that populates the dropdown?

If your VBA is already opening the Excel source file, it should be fairly easy to read the text description on the fly based on the choice in the dropdown.

Thanks for your reply. My code looks like this:

Sub Document_Open()
Application.ScreenUpdating = False
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
Dim StrWkBkNm As String, StrWkShtNm As String, LRow As Long, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Dropdown Information.xlsx"
StrWkShtNmTestNo = "Test Number"

With xlApp
  .Visible = False
  
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False)
  
  With xlWkBk
      With .Worksheets(StrWkShtNmTestNo)
        LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        ActiveDocument.SelectContentControlsByTitle("TestNo")(1).DropdownListEntries.Clear
        For i = 2 To LRow
          ActiveDocument.SelectContentControlsByTitle("TestNo")(1).DropdownListEntries.Add _
            Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
        Next
      End With

    .Close False
  End With
  .Quit
End With
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Here, the dropdown options are in column A, and upon selecting them I want the descriptions from column B to be entered into a plain text content control.

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Neil Fleming (https:#a42365790)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer