Substitute a variable in a loop

  • Thread starter Thread starter TheMike
  • Start date Start date
T

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
 
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
 
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
 
For i = 1 To 3
ActiveCell.Offset(0, 2) = Me.Controls("cMake" & Format(i, "00")).Text
Next
 
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
 
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
 
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
 
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!
 
Bob Phillips said:
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!
Click to expand...

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
 
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.
 
Back
Top