Use a variable to select an ActiveX control Checkbox by name (in Word from Excel)?

Question

What is wrong with this line of code?

WordDoc.CheckBoxNum.Value = CheckBoxVal

To be clear, I am working with an ActiveX control "Checkbox". I have the values (true/false) and checkbox names saved in an Excel file. The code opens my word document and automatically loops through the checkboxes, setting their values. The fact I am controlling word from Excel may change my objects?

If they were Form Field... This works--

CheckBoxNum = "Check1"
CheckBoxVal = "True"
WordDoc.FormFields(CheckBoxNum).CheckBox.Value = CheckBoxVal

Here is my whole code

Sub Doit()
    Dim WordApp As Object
    Dim WordDoc As Object
    Dim filepath As String
    Dim CheckBoxNum As String
    Dim CheckBoxVal As String
    Dim i As Integer, k As Integer

    'Open Word
    filepath = "C:\test.docx"
    Set WordApp = CreateObject("word.application")
    WordApp.Visible = True
    Set WordDoc = WordApp.Documents.Open(filepath)

    'Editing
    Range("a1").Select 'first value
    i = 0
    For i = 0 To 6 ' number of rows in table 
          'get from excel
          CheckBoxNum = ActiveCell.Offset(i, k + 1).Value
          CheckBoxVal = ActiveCell.Offset(i, k).Value

           'put to word
           WordDoc.CheckBoxNum.Value = CheckBoxVal '<-- HELP!
    Next i

    '// CLEAN UP //
    WordDoc.Application.ActiveDocument.Save
    WordDoc.Close
    WordApp.Quit
    Set WordDoc = Nothing
    Set WordApp = Nothing
End Sub

Andy G Andy G · Accepted Answer · 2014-06-06T19:35:43

Unfortunately we cannot refer to an ActiveX control directly by its name using WordDoc.InlineShapes(CheckBoxNum), this only works if we know its index number: WordDoc.InlineShapes(1).

This means that you have to loop through all the controls, comparing its OLEFormat.Object.Name to the name you are looking for:

Dim obj As Object

For i = 0 To 6 ' number of rows in table
    'get from excel
    CheckBoxNum = ActiveCell.Offset(i, k + 1).Value
    CheckBoxVal = ActiveCell.Offset(i, k).Value

    For Each obj In WordDoc.InlineShapes
        If obj.OLEFormat.Object.Name = CheckBoxNum Then
            obj.OLEFormat.Object.Value = CheckBoxVal
        End If
    Next obj
Next i

Use a variable to select an ActiveX control Checkbox by name (in Word from Excel)?

What is wrong with this line of code?

Here is my whole code

2 Answers