Substitute a variable in a loop
 

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 a
"cMake01.Value"....."cMake03.Value"

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

regards Mike

Substitute a variable in a loop
 

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

TheMike wrote:

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 a
"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

Substitute a variable in a loop
 

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
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"TheMike" wrote:

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 a
"cMake01.Value"....."cMake03.Value"

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

regards Mike

Substitute a variable in a loop
 

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


--
__________________________________
HTH

Bob

"TheMike" wrote in message
...
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 a
"cMake01.Value"....."cMake03.Value"

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

regards Mike

Substitute a variable in a loop
 

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" wrote in message
...
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 a
"cMake01.Value"....."cMake03.Value"

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

regards Mike

Substitute a variable in a loop
 

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

On 6 jan, 22:30, Dave Peterson wrote:
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



TheMike wrote:

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 a
"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

Substitute a variable in a loop
 

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

"TheMike" wrote in message
...
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

On 6 jan, 22:30, Dave Peterson wrote:
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



TheMike wrote:

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 a
"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

Substitute a variable in a loop
 

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!

--
__________________________________
HTH

Bob

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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 a
"cMake01.Value"....."cMake03.Value"

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

regards Mike

Substitute a variable in a loop
 

"Bob Phillips" wrote in message

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

Substitute a variable in a loop
 

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.

--
__________________________________
HTH

Bob

"Peter T" <peter_t@discussions wrote in message
...
"Bob Phillips" wrote in message

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