Sorting Controls by Name
 

Like when you click the ok button on the userform?

Option Explicit
Private Sub CommandButton1_Click()
Dim myName As String
Dim ctrl As Control

Range("A1").Select
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.SpinButton Then
myName = Mid(ctrl.Name, 5) 'not like =mid() in the worksheet
ActiveCell.Value = Me.Controls("lab" & myName).Caption
ActiveCell.Offset(0, 1) = Me.Controls("txt" & myName).Text
ActiveCell.Offset(1, 0).Select
If ActiveCell.Row = 10 Then
ActiveCell.Offset(-10, 1).Select
End If
End If
Next
End Sub

I wouldn't use a variable named Name. It may be ok with VBA, but it would
confuse me.

BOBODD wrote:

I'm not sure of appropriate syntax on these forums, so I've noted comments or
what I'm trying to do in {} brackets.

How would I do this:

Private Sub FillForm()
Dim Name As String
Range("A1").Select
For Each SpinButton in UserForm
Name = Mid(Spinbutton.Name, 5, {However long Spinbutton.Name is})
ActiveCell = Label.Value {Where Label.Name = "lab" & Name}
ActiveCell.Offset(0, 1) = Textbox.Value {Where Textbox.Name = "txt"
& Name}
ActiveCell.Offset(1, 0).Select
If ActiveCell.Row = 10 Then
ActiveCell.Offset(-10, 1).Select
End If
Next
End Sub

I've been banging my head over this one for several days now. I can call
each control individually, but I have a lot of controls and I end up with a
Sub that's several pages in length.

--

Dave Peterson