Changing userform control's index?

[gravanoc]

I have a routine that I'm currently doing in a very manual way by referencing each control by name, whereas I'd like to be able to just iterate through the controls on the form, get their value & plug it into the sheet at the appropriate slot. If I reference the Controls collection it seems like they are indexed by when they were created. Can I change this, or is there a better way to re-write this code?
One major reason for wanting to restructure this is because some of the controls have default values that I don't want saved to the sheet. It would be much easier to stop that from happening with a loop.

Public Sub SaveActivityDetails()
   
    Dim rngActivity As Range, Target As Range
    Dim attr(0 To 23) As Variant
    Dim i As Long
   
    Set Target = ActiveCell
    Set rngActivity = wksTracker.Range("C" & Target.Row, "X" & Target.Row)
   
    rngActivity.Cells(1, 3).Value = ActivityDetails.tbCISActivity
'    rngActivity.Cells(1, 21).Value = ActivityDetails.tbCISDescription
    rngActivity.Cells(1, 22).Value = ActivityDetails.tbCISTotal
    rngActivity.Cells(1, 1).Value = ActivityDetails.cbCISGTM
    rngActivity.Cells(1, 6).Value = ActivityDetails.cbCISLoC
    rngActivity.Cells(1, 7).Value = ActivityDetails.cbCISMgr
    rngActivity.Cells(1, 11).Value = ActivityDetails.cbCISIO
    rngActivity.Cells(1, 12).Value = ActivityDetails.cbCommitInfo
    rngActivity.Cells(1, 15).Value = ActivityDetails.tbCommitInfoStartDate
    rngActivity.Cells(1, 4).Value = ActivityDetails.cbCommitInfoVendor
    rngActivity.Cells(1, 14).Value = ActivityDetails.tbCommitInfoPO
    rngActivity.Cells(1, 8).Value = ActivityDetails.tbCommitInfoCategory
    rngActivity.Cells(1, 16).Value = ActivityDetails.tbCommitInfoEndDate
    rngActivity.Cells(1, 17).Value = ActivityDetails.tbCommitInfoSOW
    rngActivity.Cells(1, 9).Value = ActivityDetails.tbCommitInfoAccount
    rngActivity.Cells(1, 18).Value = ActivityDetails.cbCommitInfoPII
    rngActivity.Cells(1, 19).Value = ActivityDetails.cbTrackerActivity
    rngActivity.Cells(1, 20).Value = ActivityDetails.cbTrackerTransfer
    rngActivity.Cells(1, 21).Value = ActivityDetails.cbTrackerItemStatus
    rngActivity.Cells(1, 5).Value = ActivityDetails.cbCISOwner
    rngActivity.Cells(1, 10).Value = ActivityDetails.cbCISProgram
    rngActivity.Cells(1, 2).Value = ActivityDetails.cbCISBreakdown
    rngActivity.Cells(1, 13).Value = ActivityDetails.cbCommitInfoXCharge
    rngActivity.Cells(1, 39).Value = ActivityDetails.tbCommitInfoNotes
   
End Sub

 

[GWteB]

You might consider using the tag property of the affected controls for the column number. That way you can easily perform the desired checks in a loop.
Below is a somewhat limited example.

    Dim ctl As MSForms.Control
    For Each ctl In Me.Controls
        If VBA.IsNumeric(ctl.Tag) Then
            rngActivity.Cells(1, ctl.Tag).Value = ctl.Value
        End If
    Next ctl