Update the name of text box with specific range

[zinah]

Hi,

I have 10 columns and each column has a different name. I have updated the Name Box as "Employee_Data" so I can reference this range in my macro. Based on these columns names, I need to update text boxes, i.e. Box 1 = "EE ID", Box 2= "EE Name", and so on.
I'm very new to macros, what I need to know is how to loop through the range "Employee_Data" and name each box with the name of each column?


Thanks!

 

[DanteAmor]

Try this.
All your textboxes should be called "TextBox" and the consecutive number from 1 to 10: "TextBox1", "TextBox2", "TextBox3", etc.

Private Sub UserForm_Activate()
    i = 1
    For Each col In Range("Employee_Data").Columns
        Me.Controls("TextBox" & i).Value = col.Cells(1, 1)
        i = i + 1
    Next
End Sub

 

[zinah]

Hi,

Thank you for your respond, I got this message "Compile error: invalid use of Me keyword" and highlighted below row:

Me.Controls("TextBox" & i).Value = col.Cells(1, 1)

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[zinah]

@DanteAmor thank you so much for your usual support, you always the first to answer any inquiry I post here, I really appreciate that. I believe this macro worked perfectly fine:

Sub populate_EMPLOYEE_Info()    Set aSht = ActiveSheet
    Set rSht = Sheets("Role Scorecard")


    Dim shp As Shape
    Dim lblRng As Range, EmplRng As Range
    
        Set lblRng = Sheets("ref.").[LabelRange]
        Set EmplRng = Sheets("ref.").[EmplInfoRange]
        i = 0
        
    For lbl = lblRng.Row To (lblRng.Row + lblRng.Rows.Count - 1)
        i = i + 1
        
        For Each shp In ActiveSheet.Shapes
        If InStr(1, shp.Name, "Empl_" & i & "_Lbl") > 0 Then
            With shp
                .TextFrame2.TextRange.Characters.Text = Sheets("ref.").Cells(lbl, lblRng.Column).Value
                .TextFrame2.TextRange.Font.Bold = msoTrue
            End With
        End If
        
        If InStr(1, shp.Name, "Empl_" & i & "_Txt") > 0 Then
            With shp
                .TextFrame2.TextRange.Characters.Text = Sheets("ref.").Cells(lbl, EmplRng.Column).Value
                .TextFrame2.TextRange.Font.Bold = msoFalse
            End With
        End If
        
        Next shp
    Next lbl
End Sub

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.