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?

 

[Kenneth Hobson]

You have lots going on there with parts missing. Variable types are important. Terminology is important. Row1 cell values can be thought of as "Field Names" if you want to think in database terms. From your code, it appears that you mean combobox control names.

Try to learn from the concepts that I will demonstrate. I will use two arrays with types of Control and Range. Set is used to set those objects in the array elements. Run this from a blank sheet. Add a Userform and 3 combobox controls. Double click the userform and add this code and then run it from a blank worksheet. Obviously, you can use this method for other types of arrays and array elements.

Private Sub UserForm_Initialize()
  Dim a(1 To 3) As Control
  Dim b(1 To 3) As Range
  Dim i As Integer
  
  Set a(1) = Me.Controls("ComboBox1")
  Set a(2) = Controls("ComboBox2")
  Set a(3) = Controls("ComboBox3")
  Set b(1) = Range("A2")
  Set b(2) = Range("B4")
  Set b(3) = Range("C6")
  
  For i = 1 To 3
    b(i).Value = i + 10
    a(i).AddItem b(i).Value
  Next i
End Sub

You should probably put your routine into a separate Sub and send the two input parameters in a loop as I demonstrated.

 

[USAMax]

Thank you Ken but you are going in a different direction.

I was thinking about using an array to pass these. I know this will not work but to give you an idea of where I am going:

     MyArray = Array(LeadEngineer, ImplementationEngineer, PreconfigurationEngineer)

    For Each ComboBoxField in MyArray
        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
                ComboBoxField.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
    Next ComboBoxField

The problems are:
How do I declare a variable that will hold these Field Names? (Yes Ken I know this is the wrong Terminology but if I knew the correct terminology I might be able to search for the solution)
How do I pass the second variable?

 

[Kenneth Hobson]

How am I going in a different direction? I gave you an array method showing how to set the types of data in the arrays and more.

If you hover the mouse over a control in the toolbox when you add them, you can tell what types they are. Open the properties window for more information about each control.

Here is another example showing the last part where I said you could send data to another routine.

Private Sub UserForm_Initialize()
  Dim a() As String, b() As Variant, i As Integer
  a() = Split("ComboBox1,ComboBox2,ComboBox3", ",")
  b() = Array(2, 4, 6)
  
  ' Fill some dummy data
  For i = LBound(a) To UBound(a)
    Cells(2, b(i)).Value = i
  Next i
  
  For i = LBound(a) To UBound(a)
    FillMyComboBoxes Controls(a(i)), CLng(b(i))
  Next i
End Sub

Sub FillMyComboBoxes(cb As ComboBox, sCol As Long)
  cb.AddItem Cells(2, sCol)
End Sub

Post your workbook to a shared site like box.net if needed.

 

[USAMax]

Thank you Kenneth! I got enough out of this that I am sure it will work.

The main thing I was looking for is, "As ComboBox"!

The rest of it I have got to and would have eventually gotten it once I had that.

One last request, can you help me out with the Terminology that I should have used? I was basically calling all the ComboBox names Field Names. If I was looking for, "As ComboBox" or TextBox, what should I call those names that ComboBox will be holding?

This will allow my questions to be more articulate.

Thank you again Ken.

 

[USAMax]

I thought I understood it Ken but I am getting an error, "Compile error: Expected array". The error is pointing to the EgCol(x) in the For Next loop.

Dim EgPos As String
Dim EgCol As Integer
    Set Aws = Sheets("Available")
    'NewStrDte
    ALEC = EngPos(Aws, 1, "LEAD")                           'Get Lead Engineer Column on Available worksheet
    AIMC = EngPos(Aws, 1, "IMPLEMENTATION")                 'Get Implementation Engineer Column on Available worksheet
    APCC = EngPos(Aws, 1, "PRECONFIG")                      'Get Preconfiguration Engineer Column on Available worksheet
    APDC = EngPos(Aws, 1, "PREDEPLOYMENT")                  'Get Predeployment Engineer Column on Available worksheet
    ADEC = EngPos(Aws, 1, "DE")                             'Get Development Engineer Column on Available worksheet
    SDR = SoD(Aws, 1, DateValue(CurStrDte))                    '
    
    EgCol = Array(ALEC, ALEC, AIMC, APCC, APDC, ADEC)
    EgPos = Split("LeadEngineer, SecondEngineer, ImplementationEngineer, PreconfigurationEngineer, PredeploymentEngineer, DesignEngineer", ",")
    
    For x = 1 To 6                                          '
        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, EgCol(x)), _
                                  CurStrDte, _
                                  CurEndDte, _
                                  TmpR, _
                                  EgCol(x)) Then           '
                  Call FillMyComboBox(EngPos(x), TmpR, EgCol(x)) 'YES: Add the Lead Eng to the list.
            End If                                          '
            TmpR = TmpR + 1                                 '     Advance to the next row
        Loop                                                'CHECK AGAIN
    Next x                                                  '

I tried putting the () after EgCol and EgPos when assigning the arrays but it gave the same message, "Expected Array"

 

[Kenneth Hobson]

The control names can been seen in the mouseover text or from the Property called Name. When I am working in the VBE, Visual Basic Editor, I keep the Property view open while working in the Userform object or with the control objects. Objects can have methods and properties. Select the VBE menu View to turn on the property view. Many properties can be set manually there which skip the need for VBA code on ocassion.

 

[USAMax]

Hey Ken,

I tried to build my program around your suggestion but every time it gets to the second string of the ComboBox I get the same error: Run-time error '-2147024809 (80070057)': Could not find the specified object.

I removed the second ComboBox name from the list and it failed on the third. I removed the third ComboBox name from the list and it failed on the forth. The names work when I use them in AddItem and they are in the property box next to (Name) so I have it correct.

I have over 40 lines of code that should only be about 6 lines and I hate sloppy coding like this. Does it HAVE TO be passed in a Sub?

 

[USAMax]

Without sending it off to a Sub I tried to run this locally.

  Dim a() As String, b() As Variant, i As Integer, cr As Integer
  Dim Rws As Worksheet
  Dim TmpR As Integer
  Set Rws = Sheets(ActiveSheet.Name)
  Dim Aws As Worksheet                                        'Create object for Available worksheet
  Dim cb As ComboBox
    Set Aws = Sheets("Available")
    'NewStrDte
    ALEC = EngPos(Aws, 1, "LEAD")                           'Get Lead Engineer Column on Available worksheet
    APCC = EngPos(Aws, 1, "PRECONFIG")                      'Get Preconfiguration Engineer Column on Available worksheet
    APDC = EngPos(Aws, 1, "PREDEPLOYMENT")                  'Get Predeployment Engineer Column on Available worksheet
    ADEC = EngPos(Aws, 1, "DE")                             'Get Development Engineer Column on Available worksheet
    AIMC = EngPos(Aws, 1, "IMPLEMENTATION")                 'Get Implementation Engineer Column on Available worksheet
    SDR = SoD(Aws, 1, DateValue(CurStrDte))                    '
    
    ProjectName = CurPrjNam                                 'Get Project Name
  
  a() = Split("LeadEngineer, PreconfigurationEngineer, PredeploymentEngineer, DesignEngineer, ImplementationEngineer", ",")
  b() = Array(ALEC, APCC, APDC, ADEC, AIMC)
  

  For i = LBound(a) To UBound(a)
    Set cb = a(i)
    TmpR = SDR
    Do While Aws.Cells(TmpR, 1) = DateValue(CurStrDte) And _
             Aws.Cells(TmpR, b(i)) <> ""                    'Is the Second Eng not blank and does the Starting Date Row match the Starting Date?
        If EngFreeTrghEndDate(Aws, Aws.Cells(TmpR, b(i)), CurStrDte, CurEndDte, TmpR, Int(b(i))) Then
            cb.AddItem Aws.Cells(TmpR, b(i))          'YES: Add the Second Eng to the list.
        End If
        TmpR = TmpR + 1                                     '     Advance to the next row
    Loop                                                    'CHECK AGAIN
    'FillMyComboBoxes Controls(a(i)), Int(b(i))
  Next i

It fails when it begins with type mismatch with Set cb = a(i) selected. I set a() as Variant but it fails when it gets to the Set cb = a(i). I cannot pass a second variable your way and I get a type mismatch my way.

 

[Kenneth Hobson]

Your problems are due to not passing the correct Type of variable or object.

To set a control object using string, as I demonstrated in my code, replace your:

Set cb = a(i)

with

Set cb = Controls(a(i))

As for the 2nd input, I don't know your data. Try stepping through the code by pressing F8 to see each line execute. Hover the cursor over the value in question to see if it is returning a valid value. Obviously, cells(1, 0) would fail. Debug.Print somevariable will put the somevariable value into the Immediate window when you run the code.

Your 2nd input should be an integer for a column or column letter(s) for cells(). For the rows input in cells() be sure to always use Long variables.