Looping Variable Names

Question

This is entirely non-critical, but it's annoying me!

I have a set of textboxes on a form, called sm1, sm2 etc, and I want to assign them to cells A1, A2, etc. Is there a way to put this in a loop, ie:

for i = 1 to 100

cells(i,1).Value = ("sm"&c).Value

next i

Thoughts?

Answer 1

Not sure of VBA, but there should be "controls" collection on the form, you can access the elements of this by the control name, like you show above.

cells(i,1).Value = Controls("sm"&c).Value

Answer 2

With the textboxes on a form you can use the Controls collection.

For Each oControl In Me.Controls
    If Typename(oControl) = "TextBox" Then
        iCellNumber = Val(Mid$(oControl.Name, 3)) 'Assumes all textboxes have two letter names
        cells(iCellNumber ,1).Value = val(oControl.Text)
    End If
Next oControl

If the controls are on the sheet, you can use the shapes collection on that sheet

For Each oControl In Me.Shapes
    If InStr(oControl.Name, "TextBox") = 1 Then
        iCellNumber = Val(Mid$(oControl.Name, 3))
        cells(iCellNumber ,1).Value = val(oControl.Text)
    End If
Next oControl

Answer 3

An alternative method would be to use the ControlSource property of each textbox to bind them to the relevant cells