Substitute a variable in a loop

[TheMike]

I have a multiple text boxes in a form (excel 2000) that are named:
cMake01, cMake02 cMake03 etc..

I want to save the info from my form to a worksheet.

instead of:
ActiveCell.Offset(0, 1) = cMake01.Value
ActiveCell.Offset(0, 2) = cMake02.Value
ActiveCell.Offset(0, 3) = cMake03.Value

I want to do it in a loop:
For i = 1 To 3
ActiveCell.Offset(0, 2) = "cMake0" & i & ".Value"
Next
But now the values in my worksheet are:
"cMake01.Value"....."cMake03.Value"

What is the correct syntax to save the content of the text box and not
the name?

regards Mike

 

[Dave Peterson]

On a userform?

dim iCtr as long
for ictr = 1 to 3 'same number as your textboxes.
activecell.offset(0,ictr).value _
= me.controls("cmake" & format(ictr,"00")).value
next ictr

 

[Michael]

You need the item in the control form; something like this:

For i = 1 To 3
ActiveCell.Offset(0, i) = UserForm1.Controls.Item(i).Value
Next i

 

[Bob Phillips]

For i = 1 To 3
ActiveCell.Offset(0, 2) = Me.Controls("cMake" & Format(i, "00")).Text
Next

 

[Peter T]

Dim i As Long
For i = 1 To 3
ActiveCell.Offset(0, i) = _
Me.Controls("cMake" & Right$("0" & i, 2)).Text
Next

Regards,
Peter T

 

[TheMike]

Thanks Dave,
Your solution came closest, problem solved, thank you very much!
Can you explain to me whats the "me." part in of "me.controls("cmake"
& format(ictr,"00")).value" stands for?
Regards Mike

 

[Bob Phillips]

Me refers to the containing class, the userform in this case.

Me in worksheet event code refers to the worksheet, in workbook event code
refers to the workbook.

--
__________________________________
HTH

Bob

Thanks Dave,
Your solution came closest, problem solved, thank you very much!
Can you explain to me whats the "me." part in of "me.controls("cmake"
& format(ictr,"00")).value" stands for?
Regards Mike

 

[Bob Phillips]

I see you did it again <g>

When I posted there were no others, but now Dave's is timed at nearly 40
minutes earlier, Michael's at nearly 20. How odd!

 

[Peter T]

I see you did it again <g>

When I posted there were no others, but now Dave's is timed at nearly 40
minutes earlier, Michael's at nearly 20. How odd!

Yep, seems like it, so not just me then. I know there's often a delay up to
20 minutes but normally not hours (in my case with two posts last night).

Regards,
Peter T

 

[Bob Phillips]

I normally see them within 5 minutes, and I do check as I try to avoid
posting an answer that someone has already given. But I just didn't see
these, and Dave's is almost identical to mine.