Difficult VBA Query
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. |
Difficult VBA Query
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","R ejected") 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 |
All times are GMT +1. The time now is 03:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com