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

 

[Norie]

Scott

That code populates Combobox2 when Combobox1 changes.

Do you mean something else?

 

[ScottyG]

That is exactly what I mean but it doesn't populate until the user puts his mouse cursor onto the combobox 2 and clicks on it.

 

[Norie]

Yes it does.

 

[ScottyG]

Not on my computer, I'm still running Office 97, would that have something to do with it?

 

[Norie]

Scott

That code really does populate Combobox2.

And I see no reason why running Office 97 would make any difference.

What exactly do you mean?

 

[jindon]

ScottyG,

What do you actually wnat the combobox2 to show?

there will be multiple itmes there.

unless you specify what you really want to do, we can not do anything...

 

[ScottyG]

For Jindon

Jindon, your code works but



1. Can I make it so that as soon as the user selects the category from cboBox1, the results display in cboBox2 right away, instead of having to actually click in it. I don't know how I can be any more specific.

Say the category is set to Fruit, and different fruit is showing up in the 2nd box, if the user then selects vegetables, the fruit doesn't change to vegetables until you actually click in the second box.

Someone else kept posting that it does, but it doesn't on my computer.


But I'm more interested in the next two issues:

2. I have two other Prices associated with each item - Column E "DISCOUNT", Column F "OVERTIME" How can I also have those populate two other text boxes, txt2 and txt3? Again, not sure how much more specific I can be

3. How do I format the Text boxes to display currency properly with the $? The price shows up without the $ sign.

Thanks for your patience, Scott

 

[jindon]

1) can be set combobox2.listindex=0 and display 1st item of the category:

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 
with Me.ComboBox2 
    .Column = b 
    .listindex=0
end with
Erase a, b 
End Sub

just try the code and tell me if this is what you wanted.

3)

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

swap the code with existing one carefully...

 

[ScottyG]

You the Man!

Jindon, PERFECT!!!!

That did the trick.

Now if you could just help with one final thing. Each item actually has three different prices.

I have two other Prices associated with each item - Column E "DISCOUNT", Column F "OVERTIME" How can I also have those populate two other text boxes, named TxtBox2 and TxtBox3?

Scott

 

[jindon]

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, 5).Offset(1) ' <- changed
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 4, 1 To n) '<-
        For ii = 1 To 4  '<-
            b(ii, n) = a(i, ii + 1) 
        Next 
    End If 
Next 
Me.ComboBox2.Column = b 
Erase a, b 
End Sub

[codce]
Private Sub ComboBox2_Change()
If Me.ComboBox2.Value = "" Then
for i=1 to 3
me.controls("txt" & i).text=""
next
else
With Me.ComboBox2
for i=1 to 3
me.controls("txt" & i).text= _
format(.List(.ListIndex, i+1) ,"$#,##0.00")
next
End With
end if
End Sub [/code]