Difficult VBA Query

[Guest]

I have several userforms each with several controls in a frame or 2. I have
spin buttons or checkboxes which adjust a value in a textbox & alter a label
(or the checkbox caption) appropriately. My controls are named with this
convention: Spinbuttons are "SpinXXX", e.g "SpinRelease", labels are
"labXXX", checkboxes are "chkXXX" & textboxes are "txtXXX".

I'm using the following code to transfer the info to a worksheet:

Private Sub UpdateSchedule()
Set MyFrame = Me.Frame1
Call FillForm(3, "B16", 0, 2, 0, 0)
End Sub

Public Sub FillForm(MySheet As Integer, MyRange As String, MyRow As Integer,
MyCol As Integer, ColEnd As Integer, NewCol As Integer)
Dim MyName As String
Dim ctrl As Control
Sheets(MySheet).Select
Range(MyRange).Select
For Each ctrl In MyFrame.Controls
If Right(ctrl.Name, 1) = "1" Then
ColEnd = ColEnd - 1
End If
If ctrl <> 0 Then
Call ChkEmpty(MySheet, MyRange, MyRow, MyCol, ColEnd, NewCol)
If TypeOf ctrl Is MSForms.CheckBox Then
MyName = Mid(ctrl.Name, 4)
ActiveCell = MyFrame.Controls("chk" & MyName).Caption
ActiveCell.Offset(0, 1) = MyFrame.Controls("txt" &
MyName).Text
ActiveCell.Offset(MyRow, MyCol).Select
ElseIf TypeOf ctrl Is MSForms.SpinButton Then
MyName = Mid(ctrl.Name, 5)
ActiveCell = MyFrame.Controls("lab" & MyName).Caption
ActiveCell.Offset(0, 1) = MyFrame.Controls("txt" &
MyName).Text
ActiveCell.Offset(MyRow, MyCol).Select
End If
End If
If Right(ctrl.Name, 1) = "1" Then
ColEnd = ColEnd + 1
End If
Next
End Sub

I've since added several more controls to my frame. I want these to be
processed in a certain order, but VBA seems to automatically process them in
a "First in, first out" order so that my new controls are processed last.

My question is this: How do I affect the order that a For Each loop
processes? It doesn't appear to be affested by the tabindex which is the only
likely property I can find.

 

[Dave Peterson]

If you had named your controls like Txt001, Txt002, Spin001, Spin002, ..., you
could loop through that by varying the counter in numeric order.


dim iCtr as long
dim iCtrF as String

for ictr = 1 to 124 '
ictrF = format(ictr, "000")
msgbox me.controls("Txt" & ictrF).Text
'me.controls("SPIN" & ictrf).....
'me.controls("Lab" & ictrf)....
next ictr

But since you used strings, you'll have to find another way...

Dim mySuffixes as Variant
dim iCtr as long

'put these in order and use your suffixes
mySuffixes = array("Release","Review","Assigned","Completed","Rejected")

for ictr = lbound(mysuffixes) to ubound(mysuffixes)
msgbox me.controls("Txt" & mysuffixes(ictr)).Text
'me.controls("Lab" & mysuffixes(ictr)).caption ....
next ictr