CODEWITHSUNDEEP
excelvbaloopsuserform
BlackBear
BlackBear

Reputation: 385

Userform loops in seemingly reverse order

I have a userform of 7 checkboxes and some labels that describe them. For each corresponding checkbox there is an array from which there will be created a report if the checkbox is checked as true. However, it doesn't loop through correctly.

I want it to loop through as A, B, C, D, E, F, G for for each checkbox who have the TabIndex numbers of 0, 1, 2, 3, 4, 5, 6 respectively. However it loops through in the order of 0,6,5,4,3,2,1.

I have a main sub that defines and declares variables. My userform print code is as follows:

Sub Get_PDF_Click()
' Creating PDF

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

PDFUserForm.Hide
i = 0
j = 0
For Each ctl In Me.Controls
    If TypeName(ctl) = "CheckBox" Then
        If ctl.Value = True Then
            j = j + 1
            Name_of_File = Array(i + 1, 1) & " report" & YYMM & ".xlsx"
            Workbooks.Open Filename:=OutputPath & Name_of_File
            Set Wkb = Workbooks(Name_of_File)
                For Each ws In Wkb.Worksheets
                    PDF_Name = Array(i + 1, 1) & " " & ws.Name & " " & YYMM
                    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    OutputPath & PDF_Name, Quality _
                    :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                    :=False, OpenAfterPublish:=False
                Next ws
            Wkb.Close SaveChanges:=False
        End If ' See if checked
    i = i + 1
    Debug.Print ctl.Name
    End If ' See if checkbox
Next ctl

If j > 0 Then
    ' Notification on process time
    SecondsElapsed = Round(Timer - StartTime, 0)
    MsgBox "PDF succesfully published after " & SecondsElapsed & " seconds." & Chr(10) & "Location: " & OutputPath, vbInformation
Else
    MsgBox "No file was selected.", vbInformation
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

As an aside I have a similar problem in another piece of code where I loop through charts on a worksheet which is also looping in the wrong order, so perhaps the same solution concept can be applied to that.

Upvotes: 3

Views: 157

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Here is another way ;) This does not need you to hard code the name of the checkboxes.

Logic: Create a 2D array. Store Tabindex and CheckBox Name in the array. Sort it on Tabindex and use it as you want it :)

Code:

Option Explicit

Private Sub Sample()
    Dim CbArray() As String
    Dim n As Long: n = 1
    Dim cbCount As Long
    Dim tindex As String, ctlname As String
    Dim ctl As Control
    Dim i As Long, j As Long

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
           n = n + 1
        End If
    Next

    n = n - 1: cbCount = n

    ReDim CbArray(1 To n, 1 To 2)

    n = 1

    '~~> Sort the Tabindex and checkbox name in the array
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
           CbArray(n, 1) = ctl.TabIndex
           CbArray(n, 2) = ctl.Name
           n = n + 1
        End If
    Next

    '~~> Sort the array
    For i = 1 To cbCount
        For j = i + 1 To cbCount
            If CbArray(i, 1) < CbArray(j, 1) Then
                tindex = CbArray(j, 1)
                ctlname = CbArray(j, 2)

                CbArray(j, 1) = CbArray(i, 1)
                CbArray(j, 2) = CbArray(i, 2)

                CbArray(i, 1) = tindex
                CbArray(i, 2) = ctlname
            End If
        Next j
    Next i

    '~~> Loop through the checkboxes
    For i = cbCount To 1 Step -1
        With Controls(CbArray(i, 2))
            Debug.Print .Name
            '
            '~~> Do what you want
            '
        End With
    Next i
End Sub

Upvotes: 3

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

For Each isn't specified to guarantee an enumeration order. In all likelihood the controls are being enumerated in the order they were added to the Me.Controls collection.

If you need a specific order, use a For loop:

Dim checkboxNames As Variant
checkboxNames = Array("chkA", "chkB", "chkC", "chkD", "chkE", ...)

Dim current As Long, checkboxName As String, currentBox As MSForms.CheckBox
For current = LBound(checkboxNames) To UBound(checkboxNames)
    checkboxName = checkboxNames(current)
    Set currentBox = Me.Controls(checkboxName)
    'work with the currentBox here
Next

Note that this also removes the need to iterate controls you're not interested in

Upvotes: 3

Related Questions