Code to access Controls via variables?

I wonder if there is a way to use variables to iterate controls
(textbox) on a form via VBA i Exel, just as you can in Access
Something Like:
Dim ctrlS As Control
...
For n = 1 to 60
strSkudd = "txtSkudd"& n
Set ctrlS = Controls(strSkudd)
....
Range("A1").Offset((i + intR) - 2, 6 + j) = ctrlS.Text

....
This does not work, but is it posible, and how??

I have a form with plenty of textboxes (txtSkudd1, txtSkudd2 etc..
txtSkudd60) on which I want to write to a spreadsheet from and vice
verse .

Kjell s J

Hi Kjell,

Try something like:

Private Sub UserForm_Initialize()

Dim i As Long

For i = 1 To 4
With Sheets("Sheet2")

'Copy from worksheet to text boxes
UserForm1.Controls("Skedd" & i).Text = _
.Cells(i, "A").Value

'copy from text boxes to worksheet
Cells(i + 10, "D") = _
UserForm1.Controls("Skedd" & i).Text

End With
Next

End Sub



---
Regards,
Norman



"Kjell S. Johansen" <[email protected]> wrote in message
news:[email protected]...
>I wonder if there is a way to use variables to iterate controls
> (textbox) on a form via VBA i Exel, just as you can in Access
> Something Like:
> Dim ctrlS As Control
> ..
> For n = 1 to 60
> strSkudd = "txtSkudd"& n
> Set ctrlS = Controls(strSkudd)
> ...
> Range("A1").Offset((i + intR) - 2, 6 + j) = ctrlS.Text
>
> ...
> This does not work, but is it posible, and how??
>
> I have a form with plenty of textboxes (txtSkudd1, txtSkudd2 etc..
> txtSkudd60) on which I want to write to a spreadsheet from and vice
> verse .
>
> Kjell s J

Hi Kyell,

To accord with your code,

Change both instances of: "Skedd" to: "txtSkudd"

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Kjell,
>
> Try something like:
>
> Private Sub UserForm_Initialize()
>
> Dim i As Long
>
> For i = 1 To 4
> With Sheets("Sheet2")
>
> 'Copy from worksheet to text boxes
> UserForm1.Controls("Skedd" & i).Text = _
> .Cells(i, "A").Value
>
> 'copy from text boxes to worksheet
> Cells(i + 10, "D") = _
> UserForm1.Controls("Skedd" & i).Text
>
> End With
> Next
>
> End Sub
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "Kjell S. Johansen" <[email protected]> wrote in message
> news:[email protected]...
>>I wonder if there is a way to use variables to iterate controls
>> (textbox) on a form via VBA i Exel, just as you can in Access
>> Something Like:
>> Dim ctrlS As Control
>> ..
>> For n = 1 to 60
>> strSkudd = "txtSkudd"& n
>> Set ctrlS = Controls(strSkudd)
>> ...
>> Range("A1").Offset((i + intR) - 2, 6 + j) = ctrlS.Text
>>
>> ...
>> This does not work, but is it posible, and how??
>>
>> I have a form with plenty of textboxes (txtSkudd1, txtSkudd2 etc..
>> txtSkudd60) on which I want to write to a spreadsheet from and vice
>> verse .
>>
>> Kjell s J
>
>

"Norman Jones" <[email protected]> wrote:
Looks as if it is working. Thank you very much

Kjell

>Hi Kyell,
>
>To accord with your code,
>
>Change both instances of: "Skedd" to: "txtSkudd"
>
>---
>Regards,
>Norman
>
>
>
>"Norman Jones" <[email protected]> wrote in message
>news:[email protected]...
>> Hi Kjell,
>>
>> Try something like:
>>
>> Private Sub UserForm_Initialize()
>>
>> Dim i As Long
>>
>> For i = 1 To 4
>> With Sheets("Sheet2")
>>
>> 'Copy from worksheet to text boxes
>> UserForm1.Controls("Skedd" & i).Text = _
>> .Cells(i, "A").Value
>>
>> 'copy from text boxes to worksheet
>> Cells(i + 10, "D") = _
>> UserForm1.Controls("Skedd" & i).Text
>>
>> End With
>> Next
>>
>> End Sub
>>
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Kjell S. Johansen" <[email protected]> wrote in message
>> news:[email protected]...
>>>I wonder if there is a way to use variables to iterate controls
>>> (textbox) on a form via VBA i Exel, just as you can in Access
>>> Something Like:
>>> Dim ctrlS As Control
>>> ..
>>> For n = 1 to 60
>>> strSkudd = "txtSkudd"& n
>>> Set ctrlS = Controls(strSkudd)
>>> ...
>>> Range("A1").Offset((i + intR) - 2, 6 + j) = ctrlS.Text
>>>
>>> ...
>>> This does not work, but is it posible, and how??
>>>
>>> I have a form with plenty of textboxes (txtSkudd1, txtSkudd2 etc..
>>> txtSkudd60) on which I want to write to a spreadsheet from and vice
>>> verse .
>>>
>>> Kjell s J
>>
>>
>
>