VBA textbox name property

[WhiteD3]

Guru needed:)

I've got an Excel form with 10 textboxes named tbBox1, tbBox2, etc.

In my VBA code I want to run a loop that looks through a sheet and when it finds a value, populate that value into tbBox1, keep looking and when it finds then next value populate tbBox2, etc, etc.

What I'm after is a way to dynamically change the textbox I'm referring to instead of individually naming them.

ie
For i= 1 to 10
MyName = "tbBox" & i
MyName.Value = Worksheets("Stock List").Cells(j, Stock_Column).Value


Of course, this doesn't work. Any ideas please?

[dmdigital]

Yes there are a couple of ways, either use a switch/case statement based on the value of i to direct to the correct object or use the translate object name (can't remember the exact syntax:() based on "tbBox" & i to load the correct textbox for storing to.

Don't have Excel at home now only at work, so can't look it up.

[Ferret]

Assuming the only text boxes on your form are the 10 text boxes in question then loop through the controls collection on your form

j=0
For Each cntl in Me.Controls
If TypeName(cntl) = "TextBox" Then
cntl.Value = Worksheets("Stock List").Cells(j, Stock_Column).Value
j=j+1
End If
Next cntl