Use Word VBA to set Content Control from Excel Spreadsheet

I'm working on a Word document that has a dropdown list and a plain text content control, I want the plain text to be filled based on the dropdown selection. I have the dropdown entries and plain text description in an Excel document. I have been able to successfully do this by assigning the description to the value of the dropdown entry, but the character limit is too small. Is there any way to implement this?
Thanks!
Vikas KiniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
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.
0
Vikas KiniAuthor Commented:
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

Open 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.
0
Neil FlemingConsultant and developerCommented:
This is tricky, as you say, because a) there is an unchangeable limit on the number of characters in the value property and b) EVEN WORSE Microsoft has for mysterious reasons not created an "onChange" event for contentControl dropdown lists.

The attached file achieves what you want, however.

I modified your list populator and put it in a separate module called "mList". The routine now reads from the first Excel column into the dropdown, but stores the second column in an array.

As follows:
Option Explicit
Public aText() As Variant

Sub PopList()
Application.ScreenUpdating = False
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook, xlSheet As Excel.Worksheet, xlR As Excel.Range
Dim dd As ContentControl
Dim StrWkBkNm As String, StrWkShtNm As String, LRow As Long, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Dropdown Information.xlsx"
'StrWkBkNm = "C:\Neil Work\software\excel\expertsexchangequestions\Dropdowncontents.xlsx"
StrWkShtNm = "Test Number"

With xlApp
  .Visible = False
  
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False)
  Set xlSheet = xlWkBk.Worksheets(StrWkShtNm)
  With xlSheet
        LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set dd = ActiveDocument.SelectContentControlsByTitle("TestNo")(1)
        dd.DropdownListEntries.Clear
        For i = 2 To LRow
        dd.DropdownListEntries.Add Text:=Trim(.Range("A" & i))
        Next
        'get text descriptions from Excel as array
        Set xlR = .Range(.Cells(2, 1), .Cells(LRow, 2))
        aText = xlR.Value
        End With

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

End Sub

Open in new window


Then to trap changes in the dropdown, I added code to the workbook level module, that recognises when the user clicks into the dropdown and activates a looping  routine that continuously checks the dropdown for a change, and will change the contents of the text box if a change is detected. The loop ends when the user leaves the dropdown.

The problem with this approach is that if your code crashes, the array created by PopList will be lost. You could make this more robust by writing the list as XML to a Word "XMLPart" or by building in a refresh of the Poplist routine.

Here is the code from the document level module

Option Explicit
Dim sOld As String, sNew As String
Dim inControl As Boolean
Dim cc As ContentControl

Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)
If ContentControl.Title = "TestNo" Then
Set cc = ContentControl
inControl = True
sOld = ContentControl.Range.Text
CheckChange
End If
End Sub

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
inControl = False
End Sub

Private Sub Document_Open()
PopList
End Sub

Sub CheckChange()
Dim i As Long
'loop until user quits dropdown
Do
DoEvents
'check value of dropdown
sNew = cc.Range.Text
If sNew <> sOld Then
sOld = sNew
    'scan for entry in aText array, created by poplist routine
    For i = 1 To UBound(aText, 1)
    If aText(i, 1) = sNew Then
    ActiveDocument.SelectContentControlsByTitle("TextTest")(1).Range.Text = aText(i, 2)
    Exit For
    End If
    Next
End If
Loop Until (inControl = False)
Set cc = Nothing
End Sub

Open in new window


File also attached. Hope this helps.
GetItem.docm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TracyVBA DeveloperCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.