Defining Order of Controls in User Form for Loops

richh

Board Regular
Joined
Jun 24, 2007
Messages
111
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)


VBA Code:
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Put the controls in an array in the order you like and loop through them like this...

VBA Code:
    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

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Jun 24, 2007
Messages
111
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,585
Messages
5,625,656
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top