Dynamically select a TextBox based on range value

IIOII · #1 07-20-2016, 12:39 AM

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!

Code:

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 · #2 07-20-2016, 01:48 AM

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 · #3 07-20-2016, 04:16 AM

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 · #4 07-20-2016, 07:41 PM

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 · #5 07-20-2016, 08:42 PM

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:

Code:

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 · #6 07-20-2016, 08:57 PM

The basic syntax would be

Code:

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

Code:

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 · #7 07-20-2016, 10:25 PM

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 · #8 07-20-2016, 10:41 PM

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

The following works.

Code:

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 · #9 07-20-2016, 11:28 PM

Awesome! Works perfectly. Many thanks for your help!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro/VBA code to select ALL text in a textbox in microsoft excel and add a new row	jyfuller	Excel Programming	11	06-01-2015 08:49 PM
select a cluster of point from a range based on x and y values	sandcharles	Excel	5	02-19-2015 06:15 AM
Generating Print Labels Dynamically Based on SQL Server	expinch	Word	1	11-26-2014 10:05 PM
Dynamically changing drop-down list based on selection? (Word Form)	laurarem	Word	1	02-21-2013 10:17 PM
Display result in textbox based on the input of another textbox	scarymovie	Word VBA	5	05-16-2012 07:05 PM