Populate combo box based on selection

[grawls44]

Hi Everyone

I have a user form that has a combo box with 3 selections (18,21,25) Customer numbers.
I have another combo box that has a product list of around 80 items the problem is not all customers carry all the products. what I am trying to do is something like if customer = 18 then ws.(column P) <>"" then populate a combo box(I_List1) with 2 other ranges (GDR,Greg) and do this for each customer number.

customers
18 = Column P
21 = Column T
25 = Column X

I am using excel 2007
ws is another workbook that has the product list and each customer

below is the code I have that does not work.

Private Sub I_Line1_DropButt*******()


Dim ws As Worksheet
Dim c As Range


Set ws = Workbooks("Bryant's Pricing by Accounts.xlsm").Sheets("Bryant's Price List")


Me.I_Line1.RowSource = ""


With ws
    If Me.I_Cust_No = "18" Then
     For Each c In .Range("P8:P100")
        If c.Value <> "" Then
        With Me.I_Line1
        .ColumnCount = 2
        .AddItem ws.Range("GDR").Cells(c, 1).Value
        .List(.ListCount - 1, 1) = ws.Range("Greg").Cells(c, 1).Value
        End With
        End If
        Next c
    Else
    Exit Sub
End If
End With

I am using DropButt******* because it made sense to me if there is a better way please put me on the right path.

any help on this would be great

Thanks
Greg

 

[Jorge_B]

Hi,
As far as I understand, you’re trying to populate a combobox based on the value on other combobox. I mean, there’s a list for 18, other list for 21 and a different list for 25. Is it right? If so, you could try this:

Please note that this procedure must be linked to the change event of the first ComboBox (the one with 18-21-25), you are supossed to change the ranges in 7th, 8th and 9th lines to fit your needs and put the name of the first ComboBox in the 10th line.

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim List18 As Range
Dim List21 As Range
Dim List25 As Range
Set ws = Workbooks("Bryant's Pricing by Accounts.xlsm").Sheets("Bryant's Price List")
Set List18 = ws.Range("P8:P100")
Set List21 = ws.Range("T8:T100")
Set List25 = ws.Range("X8:X100")
Select Case ComboBox1
    Case 18
    ComboBox2.RowSource = List18.Address
    Case 21
    ComboBox2.RowSource = List21.Address
    Case 25
    ComboBox2.RowSource = List25.Address
End Select
End Sub

Hope it helps.

 

[grawls44]

Hi Jorge,

Thanks for the help.
That works to read the list.
2 problems
1. is there a way to remove blank vales?
2. is there a way to put 2 columns in the combo box add (Column A)?

I had the combo box populating but ti has all items in it. some customers only carry 6 items and some carry 20 items, makes it hard to pick them out of a list of 80.

Thanks again
Greg

 

[grawls44]

OK.

I got the 2 columns, not as clean as your code Jorge but it's the best I know how to do.
I still don't know how to tell it to ignore or don't populate blanks.

Private Sub I_Line2_DropButt*******()


Dim ws As Worksheet
Dim i As Long


Set ws = Workbooks("Bryant's Pricing by Accounts.xlsm").Sheets("Bryant's Price List")


If Me.I_Cust_No = "18" Then
With Me.I_Line2
    .ColumnCount = 2
    For i = 1 To 80
        .AddItem ws.Range("GDR").Cells(i, 1).Value
        .List(.ListCount - 1, 1) = ws.Range("ScCode").Cells(i, 1).Value
    Next i
End With
Else
If Me.I_Cust_No = "21" Then
With Me.I_Line2
    .ColumnCount = 2
    For i = 1 To 80
        .AddItem ws.Range("GDR").Cells(i, 1).Value
        .List(.ListCount - 1, 1) = ws.Range("PWCode").Cells(i, 1).Value
    Next i
End With
Else
If Me.I_Cust_No = "25" Then
With Me.I_Line2
    .ColumnCount = 2
    For i = 1 To 80
        .AddItem ws.Range("GDR").Cells(i, 1).Value
        .List(.ListCount - 1, 1) = ws.Range("MitCode").Cells(i, 1).Value
    Next i
End With
Else
Exit Sub
End If
End If
End If






End Sub

I put this on another combo box so i didn't mess with the code you provided, yes there are 13 combo boxes with the same code used to fill in a order form.

Greg

 

[Jorge_B]

Hi,
I adjusted the procedure to avoid blank cells being added to the combobox. Give it a try to see how it goes.

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim List18 As Range
Dim List21 As Range
Dim List25 As Range
Dim CurrCell As Range
Dim ListToUse As Range
Set ws = Workbooks("Bryant's Pricing by Accounts.xlsm").Sheets("Bryant's Price List")
Set List18 = ws.Range("A1:A10")
Set List21 = ws.Range("B1:B10")
Set List25 = ws.Range("C1:C10")
ComboBox2.Clear
Select Case ComboBox1
    Case 18
    Set ListToUse = List18
    Case 21
    Set ListToUse = List21
    Case 25
    Set ListToUse = List25
End Select
Set CurrCell = ListToUse.Cells(1, 1)
Do Until CurrCell.Row > ListToUse.Rows.Count
    If CurrCell <> vbNullString Then
    ComboBox2.AddItem CurrCell
    End If
Set CurrCell = CurrCell.Offset(1, 0)
Loop
End Sub

About adding a new column, I see you already worked it out (using the ColumnCount property), so I don’t know if you need some assistance about that.
Let me know how it goes.
Hope it helps.

 

[grawls44]

This works Great Thanks

but I can't seem to figure out how to add the new column to your code. Is there a way to add column A using .offset so that only the non blank cells in the first list are populated in the second column?

Thanks for all the help.
Greg

 

[Jorge_B]

Hi,
Sorry, but this last part isn’t very clear to me, what I understand is that you want to add a new column to the combobox and populate it with data in other column of the same worksheet. Is that right?, if so, you could try the code below.
Note that the first column of the combobox2 is populated with the data in column P, T or X (depending on the selection in combobox1); while the second column is populated with the value in column A that is located in the same row of the already added value (in column P, T or X).

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim List18 As Range
Dim List21 As Range
Dim List25 As Range
Dim CurrCell As Range
Dim ListToUse As Range
Set ws = Workbooks("Bryant's Pricing by Accounts.xlsm").Sheets("Bryant's Price List")
Set List18 = ws.Range("P1:P10")
Set List21 = ws.Range("T1:T10")
Set List25 = ws.Range("X1:X10")
ComboBox2.Clear
ComboBox2.ColumnCount = 2
Select Case ComboBox1
    Case 18
    Set ListToUse = List18
    Case 21
    Set ListToUse = List21
    Case 25
    Set ListToUse = List25
End Select
Set CurrCell = ListToUse.Cells(1, 1)
i = 0
Do Until CurrCell.Row > ListToUse.Rows.Count
    If CurrCell <> vbNullString Then
    ComboBox2.AddItem CurrCell
    ComboBox2.List(i, 1) = Range("A:A").Cells(CurrCell.Row, 1)
    i = i + 1
    End If
Set CurrCell = CurrCell.Offset(1, 0)
Loop
End Sub

Tell me if that’s what you need.
Hope it helps.

 

[grawls44]

Great.

Thanks for all the help. One day I will get the hang of all this.

Greg

 

[Jorge_B]

You're welcome. Thanks for the feedback.