CODEWITHSUNDEEP
excelvbacombobox
Trekkin
Trekkin

Reputation: 11

Dynamically populate combobox

I'm trying to populate a combobox (cbo) based on another combobox's selection.

The first cbo selection is a customer name. This populates from a table that uses cstmrId and cstmrNm. The name populates in the cbo but the cstmrId is bound.

The second cbo is for customer location. I want to limit the drop down to locations for the customer selected in the first cbo using the cstmrId the cstmr locations can be found on a different worksheet in a range named table called "tblCstmrLoc" where column 1 is cstmrId and column 2 is cstmrLoc and the table is bigger than just these two columns.

I've messed with For Each and Do While loops but missing how to limit the loop to the cstmrId that was selected in the first drop down.

Here is what I have so far:

Private Sub cboCstmr_Change()

    Dim CstmrId As Integer
    Dim Rng As Range

    CstmrId = Me.cboCstmr.ListIndex + 1
    Set Rng = Range("LISA.xlsm!tblCstmrLoc")

    Do While Rng.Value = CstmrId
        cboCstmrLoc.AddItem ActiveCell.Offset(0, 1).Value
        MsgBox ActiveCell.Offset(0, 1).Value
    Loop

End Sub

Upvotes: 0

Views: 670

Answers (1)

Trekkin
Trekkin

Reputation: 11

Here is the final code that was used

Private Sub cboCstmr_Change()
    Dim count As Long
    Dim Rng As Range
    Range("CstmrNmUsed") = cboCstmr.Text

    Me.cboCstmrLoc.Clear
    With Me.cboCstmrLoc
        For Each Rng In Range("tblCstmrLoc").Columns(1).Cells
            If CStr(Rng.Value) = Range("CstmrIdUsed") Then
                .AddItem Rng.Offset(0, 1).Value
                .List(count, 1) = Rng.Offset(0, 2).Value
                count = count + 1
            End If
        Next
    End With

End Sub

Note: I also added this line of code to my open, clear, etc. subs for the form to maintain the calculated CstmrIdUsed value.

Range("CstmrIdUsed").FormulaArray = "=SUM(IF(tblCstmr[CSTMR_NM]=CstmrNmUsed,tblCstmr[CSTMR_ID]))"

A big thanks to Thomas for helping me to work through this.

Upvotes: 1

Related Questions