Populate Dynamic ComboBoxes

**gmcconnell** · 06-09-2009, 06:07 PM

Discussion:

I had this thing working at one point and then I combined several data sheets into one and it wont work now.

PROBLEM:

When I bring the form up, the comboboxes will not populate. Also, when I had the form working originally, the comboboxes would populate with a ton of blanks (from the row count of the date).

Code:

Private Sub InitializePropertyDataCBX()

    Dim iLastRow As Integer
    Dim i        As Integer
    
    With ThisWorkbook.Worksheets("COMBOBOX DATA")
    
        FRM_TubingTracker.CBX_FromLocationName.Clear
        FRM_TubingTracker.CBX_FromLocationNumber.Clear
        FRM_TubingTracker.CBX_FromLocationAFE.Clear
        FRM_TubingTracker.CBX_ToLocationName.Clear
        FRM_TubingTracker.CBX_ToLocationNumber.Clear
        FRM_TubingTracker.CBX_ToLocationAFE.Clear
    
        iLastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        
        For i = 2 To iLastRow
            FRM_TubingTracker.CBX_FromLocationName.AddItem.Range ("C" & i)
            FRM_TubingTracker.CBX_FromLocationNumber.AddItem.Range ("D" & i)
            FRM_TubingTracker.CBX_FromLocationNumber.AddItem.Range ("E" & i)
            FRM_TubingTracker.CBX_ToLocationName.AddItem.Range ("C" & i)
            FRM_TubingTracker.CBX_ToLocationNumber.AddItem.Range ("D" & i)
            FRM_TubingTracker.CBX_ToLocationNumber.AddItem.Range ("E" & i)
        Next i
    
    End With
    
End Sub

ANTICIPATED SOLUTION:

I would love for the comboboxes to dynamically auto-populate again!

NOTES:

This is a portion of a larger post:

http://www.excelforum.com/excel-prog...-updating.html

**Andy Pope** · 06-10-2009, 05:38 AM

Rather than looping you could load the lists like this

    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

The blank rows are due to you looping for the number of rows in the used range regardless of how many items are actually in the column containing the values.

**gmcconnell** · 06-10-2009, 08:40 AM

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

**Andy Pope** · 06-10-2009, 08:57 AM

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

**gmcconnell** · 06-10-2009, 09:23 AM

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...

**gmcconnell** · 06-10-2009, 09:31 AM

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?