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

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