Populate Dynamic ComboBoxes

Oh yeah, get rid of those loops, I love it!

I still can't get it to work though? I added the bit 'Worksheets("COMBOBOX DATA")' before the '.Range' because I thought the code would need to know what sheet to look in. When that didn't work, I deleted it out of there and it still didn't work. I have included the workbook with the code in it, I don't know if I am calling the sub routine correctly or what...

 Private Sub InitializePropertyDataCBX()

   Dim vntList As Variant
    
    vntList = Worksheets("COMBOBOX DATA").Range("C2", .Cells(.Rows.Count, 3).End(xlUp))
    FRM_TubingTracker.CBX_FromLocationName.List = vntList
    FRM_TubingTracker.CBX_ToLocationName.List = FRM_TubingTracker.CBX_FromLocationName.List

    vntList = Worksheets("COMBOBOX DATA").Range("D2", .Cells(.Rows.Count, 4).End(xlUp))
    FRM_TubingTracker.CBX_FromLocationNumber.List = vntList
    FRM_TubingTracker.CBX_ToLocationNumber.List = FRM_TubingTracker.CBX_FromLocationNumber.List

    vntList = Worksheets("COMBOBOX DATA").Range("E2", .Cells(.Rows.Count, 4).End(xlUp))
    FRM_TubingTracker.CBX_FromLocationAFE.List = vntList
    FRM_TubingTracker.CBX_ToLocationAFE.List = FRM_TubingTracker.CBX_FromLocationAFE.List
    
End Sub

you have created your own Initialize routine but not called it.

A userform will always have this signature for it's Initialize event, regardless of what the actual form is called.

Private Sub UserForm_Initialize()

End Sub

so you need to either place you code in that event or call your code from that event.

Private Sub UserForm_Initialize()
    FRM_TubingTracker_Initialize
End Sub

You also need to change the other routines to use the sheet refrence/range syntax.
Along with changing the column number used to get last row for a particular column

Private Sub InitializePropertyDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
        vntList = .Range("C2", .Cells(.Rows.Count, 3).End(xlUp))
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationName.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationName.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationName.List = FRM_TubingTracker.CBX_FromLocationName.List

        vntList = .Range("D2", .Cells(.Rows.Count, 4).End(xlUp))
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationNumber.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationNumber.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationNumber.List = FRM_TubingTracker.CBX_FromLocationNumber.List
    
        vntList = .Range("E2", .Cells(.Rows.Count, 5).End(xlUp))
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationAFE.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationAFE.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationAFE.List = FRM_TubingTracker.CBX_FromLocationAFE.List
    End With
    
End Sub
'==============================================================================================
Private Sub InitializeTubingDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
        vntList = .Range("I2", .Cells(.Rows.Count, 9).End(xlUp))
    End With
    FRM_TubingTracker.CBX_TubingDescription.List = vntList

End Sub
'==============================================================================================
Private Sub InitializeSupervisorDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
        vntList = .Range("G2", .Cells(.Rows.Count, 7).End(xlUp))
    End With
    FRM_TubingTracker.CBX_Supervisor.List = vntList

End Sub
'==============================================================================================
Private Sub InitializeDateDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
        vntList = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
    End With
    FRM_TubingTracker.CBX_Date.List = vntList

End Sub

Alright, the new signature for the initialize event seems to be working, thank you - once again, something simple for so many can be such a hassle for a new programer like me.

Next, you changed your code from this:

Private Sub InitializePropertyDataCBX()    

        Dim vntList As Variant
    
        vntList = .Range("C2", .Cells(.Rows.Count, 3).End(xlUp))
        UserForm1.ComboBox1.List = vntList
        UserForm1.ComboBox11.List = UserForm1.ComboBox1.List

        vntList = .Range("D2", .Cells(.Rows.Count, 4).End(xlUp))
        UserForm1.ComboBox2.List = vntList
        UserForm1.ComboBox12.List = UserForm1.ComboBox2.List

        vntList = .Range("E2", .Cells(.Rows.Count, 5).End(xlUp))
        UserForm1.ComboBox3.List = vntList
        UserForm1.ComboBox13.List = UserForm1.ComboBox3.List

End Sub

To this:

Private Sub InitializePropertyDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
        vntList = .Range("C2", .Cells(.Rows.Count, 3).End(xlUp))
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationName.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationName.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationName.List = FRM_TubingTracker.CBX_FromLocationName.List

        vntList = .Range("D2", .Cells(.Rows.Count, 4).End(xlUp))
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationNumber.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationNumber.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationNumber.List = FRM_TubingTracker.CBX_FromLocationNumber.List
    
        vntList = .Range("E2", .Cells(.Rows.Count, 5).End(xlUp))
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationAFE.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationAFE.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationAFE.List = FRM_TubingTracker.CBX_FromLocationAFE.List
    End With
    
End Sub

Why? I see that the 'InitializeSupervisorData' etc. is still the same...

Looking at it some more, I'm thinking that it takes care of the empty list or if there are no items in row 2 for the specific sub routine, is this correct?

Yeah should have mentioned that.

The change was required as a single item will not return an array. So when trying to use it to assign the List property it will fail.

Ideally you would amend similar code to handle such occurances.

Alright, here is my code now:

'==============================================================================================
Private Sub InitializePropertyDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
        
        vntList = .Range("C2", .Cells(.Rows.Count, 3).End(xlUp))
        
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationName.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationName.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationName.List = FRM_TubingTracker.CBX_FromLocationName.List
        
        vntList = Worksheets("COMBOBOX DATA").Range("D2", .Cells(.Rows.Count, 4).End(xlUp))
        
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationNumber.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationNumber.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationNumber.List = FRM_TubingTracker.CBX_FromLocationNumber.List

        vntList = Worksheets("COMBOBOX DATA").Range("E2", .Cells(.Rows.Count, 4).End(xlUp))
        
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_FromLocationAFE.List = vntList
        Else
            FRM_TubingTracker.CBX_FromLocationAFE.AddItem vntList
        End If
        FRM_TubingTracker.CBX_ToLocationAFE.List = FRM_TubingTracker.CBX_FromLocationAFE.List
        
    End With
    
End Sub
'==============================================================================================
Private Sub InitializeTubingDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
    
        vntList = Worksheets("COMBOBOX DATA").Range("I2", .Cells(.Rows.Count, 3).End(xlUp))
        
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_TubingDescription.List = vntList
        Else
            FRM_TubingTracker.CBX_TubingDescription.AddItem vntList
        End If
    
    End With

End Sub
'==============================================================================================
Private Sub InitializeSupervisorDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
        
        vntList = Worksheets("COMBOBOX DATA").Range("G2", .Cells(.Rows.Count, 3).End(xlUp))
    
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_Supervisor.List = vntList
        Else
            FRM_TubingTracker.CBX_TubingDescription.AddItem vntList
        End If
    
    End With

End Sub
'==============================================================================================
Private Sub InitializeDateDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
    
        vntList = Worksheets("COMBOBOX DATA").Range("A2", .Cells(.Rows.Count, 3).End(xlUp))
    
        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_Date.List = vntList
        Else
            FRM_TubingTracker.CBX_Date.AddItem vntList
        End If
    
    End With

End Sub
'==============================================================================================

And here is the problem I am still having:

All sorts!

First - when the list is empty, the combobox displays the header as a choice as well as one blank for the TO and FROM comboboxes.

Second - the tubing description combobox displays a header and a blank spot even though there are (and will always be) available choices. Also, did I mention that the header it shows says 'PROPERTY NAME'

Third - the date combobox displays the header DATE and the first date but nothing else

Fourth - the supervisor combobox displays the same as the tubing description (PROPERTY NAME header with a second blank space)

now if I add some choices (3) to my combobox origin ranges (or whatever you call them) for my PROPERTY NAME, PROPERTY NUMBER, and AFE NUMBER...

First - the property comboboxes work fine, they show all 3 choices and they are 'linked' to each other. There is no header listed where there was before though

Second - the tubing description works the same as the PROPERTY NAME dropdown

Third - The date doesn't have the header anymore but only lists three dates instead of all 365

Fourth - the supervisor dropdown is the same as the PROPERTY NAME and TUBING DESCRIPTION

*****

Did I mess something simple?

If you have no items in the sheet then you will need to check that first and do whatever is appropriate for the combobox. Perhaps disable it.

The way it works is it locates the last cell by coming up the column from the last row until it hits a cell with content. For an empty column that would be row 1. The two ranges define then become row 1 and row 2. Where 1 is the Header text and 2 is the empty starting cell.

As I said you need to change the column reference to be the same. So when scanning Column I you need to use the index for column I which is 9 not 3.

Private Sub InitializeTubingDataCBX()

    Dim vntList As Variant
    
    With Worksheets("COMBOBOX DATA")
    
' WAS
'        vntList = Worksheets("COMBOBOX DATA").Range("I2", .Cells(.Rows.Count, 3).End(xlUp))
        
' SHOULD BE
        vntList = Worksheets("COMBOBOX DATA").Range("I2", .Cells(.Rows.Count, 9).End(xlUp))

        If IsArray(vntList) Then
            FRM_TubingTracker.CBX_TubingDescription.List = vntList
        Else
            FRM_TubingTracker.CBX_TubingDescription.AddItem vntList
        End If
    
    End With

End Sub

Same problem applies to the dates, where you should be index column A.