how to have a variable in userform object indicator name (VBA)

Question

I am creating a user form in VBA, and what I am looking for is to search for a specific text, when it is found, i want the textboxes to be filled from the next cells.

my code would be:

i = TextBox1.Value
Set orange = Sheets(1).Range("A1:A10000").Find(i, LookIn:=xlValues, lookat:=xlWhole)
If orange Is Nothing Then
MsgBox "No Match Found"
Else: j = orange.Address
ka = 1
kb = 11
For k = 1 To 10
UserForm1(TextBox & kb).Value = Sheets(1).Range(j).Offset(0, ka).Value
ka = ka + 1
kb = kb + 1
Next k

of course UserForm1(TextBox & kb).Value = Sheets(1).Range(j).Offset(0, ka).Value does not work.

any help with this?

Tim Williams · Accepted Answer · 2019-02-24 17:31:59Z

1

Use Me in place of the form name
You don't need all of those counter variables - just use the loop counter
Use the Controls collection to access the textboxes (passing in a string)
Indent your code

Untested:

i = TextBox1.Value
Set orange = Sheets(1).Range("A1:A10000").Find(i, LookIn:=xlValues, lookat:=xlWhole)

If orange Is Nothing Then
    MsgBox "No Match Found"
Else

    For k = 1 To 10
        Me.Controls("TextBox" & (k+10)).Value = orange.EntireRow.Cells(k).Value
    Next k

end if

answered Feb 24, 2019 at 17:31

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to have a variable in userform object indicator name (VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related