Dynamically select a TextBox based on range value

[IIOII]

Hi, I am having trouble finding a way to dynamically select a TextBox within a Word document, based on a value from a range within an Excel document.

As per the attached code, the code


1. Accesses an Excel file and sets a range to 'WordID'
2. It then loops through the range and assigns the variable 'TaskStatus'

I would then like it to:
3. Find the TextBox within Word which is named the same as the current range value (c) (i.e. If the range value = TB301, then find the TextBox named TB301" and
4. Copy TaskStatus to the current range value

When I manually call the TextBox name (i.e TB301) and update with 'TaskStatus' it works. I just can't figure out how to dynamically select the TextBox!

Private Sub CommandButton1_Click()
    
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim rng As Excel.Range
    Dim c As Excel.Range
    Dim TaskStatus As String
    
    Set exWb = objExcel.Workbooks.Open("C:\FILE.xlsm")
    Set rng = exWb.Sheets("STATUS_DATA").Range("WordID")

    For Each c In rng
        TaskStatus = c.Offset(0, 1)
        'Select TextBox that has the current range value (c) and insert (TaskStatus)
    
    Next
    
    exWb.Close
    Set exWb = Nothing
    
    Label1.Caption = "Job task status last updated: " & Date
    
End Sub

[gmayor]

What sort of text box? Form field? Content Control? Shape? ActiveX text box?

Loop through the collection that represents the text boxes and if the name matches your search string from Excel, then stop looking as you have found it.

[gmaxey]

If we can assume that you are looking for what will be there, then you can set the textbox directly.

Let's say it is a content control:
ActiveDocument.SelectContentControlsByTitle("TB301 ").Item(1).Range.Text = "whatever"

If it is not there you could use an error handler to trap the failed attempt.

[IIOII]

gmayor: I'm searching for a ActiveX textbox. You're correct in stating that I want to loop through the collection of textbox's in the Word doc until there's a match between the textbox name and the current value of 'c.' I'm stuck on the code required to use the value of 'c' as the search name for the textbox.

gmaxey: I'm not searching for the contents of the textbox, only the name. Thanks.

[gmaxey]

I never implied that you were searching for the contents of the textbox. I only implied that if you used named content controls instead of ActiveX controls and knew the named control is present in the document then you could refer to it explicitly by name.

With ActiveX controls you will have to loop:

Sub ScratchMacro(strNamePassedFromExceRange as String, strText)
'A basic Word macro coded by Greg Maxey
Dim oILS
  For Each oILS In ActiveDocument.InlineShapes
    If oILS.OLEFormat.Object.Name = strNamePassedFromExcelRange Then
      oILS.OLEFormat.Object.Value = strText
      Exit for
    End If
  Next oILS
End Sub

[gmayor]

The basic syntax would be

Dim oTB As InlineShape
    For Each oTB In ActiveDocument.InlineShapes
        If oTB.OLEFormat.Object.Name = "TB301" Then
            oTB.OLEFormat.Object.Text = "The value to be written"
            Exit For
        End If
    Next oTB

so without seeing your worksheet and in particular without knowing what 'c' refers to, you probably need something like

Private Sub CommandButton1_Click()

Dim objExcel As Object
Dim exWb As Object
Dim rng As Object
Dim c As Object
Dim TaskStatus As String
Dim oTB As Word.InlineShape


    Set xlApp = CreateObject("Excel.Application")
    Set exWb = objExcel.Workbooks.Open("C:\FILE.xlsm")
    Set rng = exWb.Sheets("STATUS_DATA").Range("WordID")

    For Each c In rng
        TaskStatus = c.Offset(0, 1)
        For Each oTB In ActiveDocument.InlineShapes
            If oTB.OLEFormat.Object.Name = c Then
                oTB.OLEFormat.Object.Text = TaskStatus
                Exit For
            End If
        Next oTB
    Next

    exWb.Close
    objExcel.Quit

    Label1.Caption = "Job task status last updated: " & Date
    Set exWb = Nothing
    Set objExcel = Nothing
    Set rng = Nothing
    Set c = Nothing
    Set oTB = Nothing
End Sub

[IIOII]

Thank you gmaxey and gmayor for your support. I have attached sample Word and Excel files to simulate what I'm trying to achieve. When I run your code gmayor, I'm getting a 'Run-time error 91: Object variable or With Block variable not set' error.
File.xlsm

Word_Sample.docm

[gmayor]

Sorry - my fault. I usually call the Excel object xlAPP and not objExcel. The code has both references The following works.

Private Sub CommandButton1_Click()

Dim objExcel As Object
Dim exWb As Object
Dim rng As Object
Dim c As Object
Dim TaskStatus As String
Dim oTB As Word.InlineShape


    Set objExcel = CreateObject("Excel.Application")
    Set exWb = objExcel.Workbooks.Open("C:\Temp\File.xlsm")
    Set rng = exWb.Sheets("STATUS_DATA").Range("WordID")

    For Each c In rng.Cells
        TaskStatus = c.Offset(0, 1)
        For Each oTB In ActiveDocument.InlineShapes
            If oTB.OLEFormat.Object.Name = c Then
                oTB.OLEFormat.Object.Text = TaskStatus
                Exit For
            End If
        Next oTB
    Next

    exWb.Close
    objExcel.Quit

    'Label1.Caption = "Job task status last updated: " & Date
    Set exWb = Nothing
    Set objExcel = Nothing
    Set rng = Nothing
    Set c = Nothing
    Set oTB = Nothing
End Sub

[IIOII]

Awesome! Works perfectly. Many thanks for your help!