CODEWITHSUNDEEP
excelvbams-word
Matt Henry
Matt Henry

Reputation: 17

Automatically selecting from content control dropdown

I'm trying to select an option from a content control dropdown in a pre built MS Word template based on values in excel. I've done this by matching the named ranges to a respective tag in the template. I was able to get all of the rich text, regular text, and checkboxes to successfully input their respective values, but when it gets to the dropdown list, it skips right past it. I've tried using the same code as the text content controls in order to proceed with the dropdown selection with no such luck. The .Tag matches the named range for all the dropdowns... I'm stumped. Here is the code. Again, all of the code works, except for the first ElseIf.

Sub Transfer()

Dim cs As Workbook
Dim wrd As Word.Application
Dim pc As Word.Document
Dim CC As ContentControl
Dim CCTag As String
Dim CStxt As String

Set cs = ThisWorkbook
Set wrd = CreateObject("Word.Application")
Set pc = wrd.Documents.Open("Template Source")


For Each CC In pc.ContentControls
    On Error Resume Next
    CCTag = CC.Tag
    If CCTag <> "" Then
        CStxt = Range(CCTag)

            If CC.Type = wdContentControlRichText Or CC.Type = wdContentControlText Then
                CC.Range.Text = CStxt

            ElseIf CC.Type = wdContentControlComboBox Or CC.Type = wdContentControlDropdownList Then
                CC.SetPlaceholderText , , CStxt

            ElseIf CC.Type = wdContentControlCheckBox Then
                    CC.Checked = False
                If CStxt = "True" Then
                    CC.Checked = True
                ElseIf CStxt = "False" Then
                    CC.Checked = False
                Else
                    CC.Checked = False
                End If
            End If

    End If
Next CC


End Sub

Upvotes: 0

Views: 488

Answers (2)

macropod
macropod

Reputation: 13515

Your code works for me - assuming each Excel range refers to a single cell. Your code could, however, be both improved and simplified:

Sub Transfer()
Dim wdApp As Word.Application, wdDoc As Word.Document, wdCC As Word.ContentControl
Dim CStxt As String

Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("Template Source")

For Each wdCC In wdDoc.ContentControls
  With wdCC
    If .Tag <> "" Then
      CStxt = ThisWorkbook.Names(.Tag).RefersToRange.Text
      Select Case .Type
        Case wdContentControlRichText, wdContentControlText
          .Range.Text = CStxt
        Case wdContentControlDropdownList, wdContentControlComboBox
          .Type = wdContentControlText
          .Range.Text = CStxt
          .Type = wdContentControlDropdownList
          '.SetPlaceholderText Text:=CStxt
        Case wdContentControlCheckBox
          If CStxt = "True" Then
            .Checked = True
          Else
            .Checked = False
          End If
        End Select
    End If
  End With
Next
End Sub

I do have to wonder, though, why you'd be modifying the placeholder text rather than selecting a combobox/dropdown item corresponding to CStxt. See code modification for a simple way to update the combobox/dropdown. For comboboxes, even that isn't needed - you could simply move ', wdContentControlComboBox' to follow 'wdContentControlText'.

Upvotes: 0

SpikeManZombie
SpikeManZombie

Reputation: 31

Firstly, I recommend that you refer to your range directly by referencing the workbook/worksheet you have declared in your variable cs This will ensure your subroutine executes on the correct workbook and correct worksheet.

If I have understood your question correctly - you would like to transfer a set of values in an Excel range over to the matching MS Word content control box - the issue would appear to be your use of

CStxt = Range(CCTag)

You cannot assign a variable of type string (CStxt) to an entire range of cells. This would generate a "Runtime error 13: Type mismatch" when executing your code.

In order to get this line to work, I had to change it to a single value/cell reference. See example below;

CStxt = cs.Sheets(1).Range("A1")

In this example, cell A1 has the value you want to transfer over to the relevant MS Word content control. This has the effect of giving the content control a display value equal to the Excel value, but is not added to the list to allow you to select it.

You can change the reference to A1 so that your named range is a single cell.

Upvotes: 0

Related Questions