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
BOBODD wrote:
>
> 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
|
Similar Threads
