Defining Order of Controls in User Form for Loops

[richh]

I am using Excel 2016. I am attempting to loop through a set of controls within a frame in a specific order and writing their content to a worksheet. I've tried defining the tab indexes, which seems to do what I need it to, for the most part, but a few controls are not looping in the correct order. I could force the program to write to specific columns, but that's not really all that dynamic; plus, I'd have to write a unique loop for each frame as their controls all differ.

The tab indexes of the controls in one of the frame are as follows:

1. Assignment - combobox (CB)
2. Type - CB
3. year - CB
4. Version - CB
5. Desc - textbox (TB)
6. Status - CB
7. ProgNum - TB
8. award - CB
9. scale - CB
10. resources - TB

It writes out as follows:

1. Assignment
2. Type
3. Year
4. Version
5. Desc
6. Status
7. Award (out of order)
8. Scale (out of order)
9. Resources (out of order)
10. ProgNum (out of order)

Private Sub sNodeButton_Click()
    Dim lRow        As Long
    Dim ws          As Worksheet
    Dim thisNode    As String
    Dim startCol    As Integer
    Dim frameName   As String
  
    startCol = 10

    Set ws = Application.ThisWorkbook.Worksheets(1)
  
    lRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
  
    For Each tNode In Me.TreeView1.Nodes 'Find the selected node and write data to its row
        If tNode.Selected = True Then
            For i = 2 To lRow
                If ws.Cells(i, 2) = tNode.Key Then
                    ws.Cells(i, 5) = "Saved"
                  
                    Select Case Left(tNode.Key, 2)
                        Case "PR"
                            frameName = "programFrame"
                        Case "PC"
                            frameName = "progCertFrame"
                        Case "CC"
                            frameName = "courseCertFrame"
                        Case "CO"
                            frameName = "courseFrame"
                        Case "MO"
                            frameName = "moduleFrame"
                        Case "CP"
                            frameName = "compentencyFrame"
                    End Select
                  
                    For Each ctrl In Me.Controls(frameName).Controls
                        If TypeName(ctrl) = "ComboBox" Or TypeName(ctrl) = "TextBox" Then
                            ws.Cells(i, startCol) = ctrl.Value
                            startCol = startCol + 1
                        End If
                    Next ctrl
                    Exit For
                End If
            Next i
            Exit For
        End If
    Next tNode
  
    Me.TreeView1.Enabled = True
    Call ResetTree
    Application.ThisWorkbook.Save
    Set ws = Nothing
End Sub

 

[AlphaFrog]

Put the controls in an array in the order you like and loop through them like this...

    Dim ctrl  As Variant
    For Each ctrl In Array(Me.Assignment, Me.Type, Me.Year, Me.Version, Me.Desc, Me.Status, _
                           Me.ProgNum, Me.award, Me.scale, Me.resources)

 

[dmt32]

Hi,
Was going to post same suggestion as AlphaFrog but beat me to it - however a query about couple of your control names Type & Scale? these I am sure are key words & would have thought the compiler would have complained?

If this is so, you may want to consider renaming them using standard naming convention by adding a prefix like cboType & cboScale

Hope Helpful

Dave

 

[richh]

I'll try AlphaFrog's solution, but that means creating a for loop for each case statement. Each frame has a different set of controls, so each array would be different.

And the control names are different than what I listed; they're "awardType" and "gradingScale". I just simplified their names above.

 

[dmt32]

I'll try AlphaFrog's solution, but that means creating a for loop for each case statement. Each frame has a different set of controls, so each array would be different.

And the control names are different than what I listed; they're "awardType" and "gradingScale". I just simplified their names above.

You can create an array of control arrays which would need only one loop

Not tested but something like following may help you

Dim ctrl As Variant, arr As Variant, m As Variant

startCol = 10

arr = Array(Array(Me.cboAssignment, Me.cboType, Me.cboYear, Me.cboVersion, Me.txtDesc, Me.cboStatus, Me.txtProgNum, Me.cboaward, Me.cboscale, Me.txtresources), _
            Array(), _
            Array(), _
            Array(), _
            Array(), _
            Array())

For Each tNode In Me.TreeView1.Nodes 'Find the selected node and write data to its row
        If tNode.Selected = True Then
            For i = 2 To lRow
                If ws.Cells(i, 2) = tNode.Key Then
                    ws.Cells(i, 5) = "Saved"
                  m = Application.Match(Left(tNode.Key, 2), Array("PR", "PC", "CC", "CO", "MO", "CP"), 0)
                  
                  If Not IsError(m) Then
                    For Each ctrl In arr(m)
                            ws.Cells(i, startCol) = ctrl.Value
                            startCol = startCol + 1
                        End If
                    Next ctrl
                 End If

Update the control names in first Array as required then Place the control names for each of the various Frames between the remaining Array brackets
I have replaced the select case statement with Match which is used to provide indexing of each of the arrays.

Solution untested but hopefully, will do what you want

Hope Helpful

Dave

PS - Almost forgot - you will need to ADD Option Base 1 at the VERY TOP of your code page