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

Code to access Controls via variables?
 

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

Code to access Controls via variables?
 

Hi Kyell,

To accord with your code,

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

---
Regards,
Norman



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

Code to access Controls via variables?
 

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