1 Combo Box filters another Combo box on a User Form

[ScottyG]

I have a dynamic database on Sheet2 named: ("DATA") with the following:

Col B, Col C, Col D
Item Code, Item, Price
FRU, PEARS, $1.00
FRU, APPLES, $1.10
VEG, SPINACH, $.99
VEG, CARROTS, $1.10
MEA, STEAK, $5.00
FRU, PEACHES, $.99
VEG, CORN, $.99

The named ranges are: Col B ("CODE") Col C ("ITEMS") Col D ("PRICE")

On Sheet1, I have a User Form with 2 combo boxes, (cbo1) lists the Item Codes. I want (cbo2) to only display the items belonging to the code selected from cbo1. Then I want the item that is selected to show it's price in a textbox (txt1)


There will be almost 1700 items in the database. Can someone please provide the code to a VBA NEWBIE? I just bought VBA for Dummies last night, so I at this point I know almost nothing but I can copy and paste well!


Scott

 

[jindon]

Hi
paste the code onto form module

Private Sub UserForm_Initialize()
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Sheets("sheet2").Range("code")
    a = .Resize(.Rows.Count - 1, 1).Offset(1).Value
End With
For Each e In a
    If Not IsEmpty(e) And Not dic.exists(e) Then _
        dic.Add e, Nothing
Next
Me.ComboBox1.List = dic.keys
Set dic = Nothing
End Sub


Private Sub ComboBox1_Change()
Dim a, i, b(), n
If Me.ComboBox1.Value = "" Then _
    Me.ComboBox2.Clear
With Sheets("sheet2").Range("code")
    a = .Resize(.Rows.Count - 1, 3).Offset(1)
End With
For i = 1 To UBound(a, 1)
    If a(i, 1) = Me.ComboBox1.Value Then
        n = n + 1: ReDim Preserve b(1 To 2, 1 To n)
        For ii = 1 To 2
            b(ii, n) = a(i, ii + 1)
        Next
    End If
Next
Me.ComboBox2.Column = b
Erase a, b
End Sub


Private Sub ComboBox2_Change()
If Me.ComboBox2.Value = "" Then
    Me.TextBox1.Text = ""
    Exit Sub
End If
With Me.ComboBox2
    Me.TextBox1.Text = _
        .List(.ListIndex, 1)
End With
End Sub

 

[ScottyG]

Thanks, I will give it a try and let you know if it works.

Scott

 

[ScottyG]

Great

Works fine but...

1. Can I make it so that as soon as the user selects the category from cboBox1 the results display in cboBox2 insead of having to actually click in it.

2. I have two other Prices associated with each item - Column E "DISCOUNT", Column F "OVERTIME" How can I have those also populate txt2 and txt3?

3. How do I format the Text boxes to display currency properly with the $?

Thanks Scott

 

[Norie]

Scott

1 Why not use a listbox instead of a combobox. Then you'll see all the items.

2 I would suggest you use a multicolumn listbox.

3 How are you populating the textboxes? Try using Text to get the formatting from the worksheet.

 

[ScottyG]

Don't want to use a list box, I want prices in separate boxes and it is already set to Text but the dollar sign isn't showing up

 

[Norie]

Scott

I don't understand what you mean by this then

1. Can I make it so that as soon as the user selects the category from cboBox1 the results display in cboBox2 insead of having to actually click in it.

 

[ScottyG]

When someone selects a different category such as vegetables instead of Fruit, I want the the vegetable list to populate in real-time instead of nothing happening until the user actually clicks on the box.

 

[Norie]

Scott

As far as I can see the code is doing that already.

Combobox2 is being populated using Combobox1's change event.

 

[ScottyG]

It doesn't actually populate until you click on it