How do I pass a field name in a UserForm as a variable.

[USAMax]

I just learned how to define a Range so I can pass it but I don't know what to call these but field names.

I have a UserForm with several comboboxes and I am searching the sheet to populate each field.

Here is what I've got:

    'LEAD ENGINEER
    TmpR = SDR                                              'Get the Starting Date Row
    Do While Aws.Cells(TmpR, 1) = DateValue(CurStrDte) And _
             Aws.Cells(TmpR, ALEC) <> ""                    'Is the Lead Eng not blank and does the Starting Date Row match the Starting Date?
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, ALEC), CurStrDte, CurEndDte, TmpR, ALEC) Then
            LeadEngineer.AddItem Aws.Cells(TmpR, ALEC)          'YES: Add the Lead Eng to the list.
        End If
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN
    
    'IMPLEMENTATION ENGINEER
    TmpR = SDR                                              'Get the Starting Date Row
    Do While Aws.Cells(TmpR, 1) = DateValue(CurStrDte) And _
             Aws.Cells(TmpR, AIMC) <> ""                    'Is the Implementation Eng not blank and does the Starting Date Row match the Starting Date?
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, AIMC), CurStrDte, CurEndDte, TmpR, AIMC) Then
            ImplementationEngineer.AddItem Aws.Cells(TmpR, AIMC) 'YES: Add the Implementation Eng to the list.
        End If
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN
       
    'PRECONFIG ENGINEER
    TmpR = SDR                                              'Get the Starting Date Row
    Do While Aws.Cells(TmpR, 1) = DateValue(CurStrDte) And _
             Aws.Cells(TmpR, APCC) <> ""                    'Is the Preconfiguration Eng not blank and does the Starting Date Row match the Starting Date?
        'Cells(TmpR, APCC).Select 'FOR TESTING ONLY
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, APCC), CurStrDte, CurEndDte, TmpR, APCC) Then
            PreconfigurationEngineer.AddItem Aws.Cells(TmpR, APCC) 'YES: Add the Preconfiguration Eng to the list.
        End If
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN

There is a lot more than this but the only things that change are the field names and one variable, the cell's column. These are what I am calling Field Names: LeadEngineer, ImplementationEngineer, PreconfigurationEngineer...

I want to create one loop for all of them but how do I pass the two variables including the Field Names?

 

[USAMax]

I cannot thank you enough for your continued support. When I try to set it as a string I still get the same error, Type mismatch with the "a" selected. I have a() dimensioned as a string with this command:

  Dim a() As String, b() As Variant, i As Integer, cr As Integer
    Set cb = a(i)

 

[USAMax]

This is a large file but maybe you can see what is happening. https://www.cubby.com/pl/My+Cubby/_dbf9a6ec9a7e467b951e76e7a342fad0

To run it select one of the color cells and run the program: UpdateProject.

Thanks again Ken.

 

[Kenneth Hobson]

You did not change it as I showed.

Set cb = Controls(a(i))