Using variables to loop through UF controls

[jproffer]

I have a userform (as you might suspect from the title ) with several (100s) of textboxes and comboboxes that will be posted to a worksheet. I'm hoping I can loop through all the controls instead of typing out each one. I tried to use this:

For i = 1 To 6
    ActiveSheet.Range("B" & i + 8).Value = TbPh1ShotNum & i.Value
Next i

but with no success. I'm almost sure it's just a syntax problem rather than a "that's not possible" problem. Does anyone know the correct way to code this?

Thanks in advance for any help you can offer.

BTW, it's telling me that TbPh1ShotNum is not a defined variable....which is true. I thought with the "& i" behind it, it would string it together, but alas .

 

[Oaktree]

If you name (by typing the desired name into the (name) field in the properties window) your text boxes on the userform to be "Textbox1", "Textbox2", etc. you can then use:

ActiveSheet.Range("B" & i + 8).Value = UserForm1.Controls("Textbox" & i).Text

or similar

 

[jproffer]

I figured it was something simple, but yet beyond my knowledge (there's a LOT of that out there ) . Worked perfectly. Thank you so much.