[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

matching 4 combo boxes

Posted on 2009-04-30
23
Medium Priority
?
319 Views
Last Modified: 2013-11-25
hello, i have 4 combo boxes.  
combo box 1 takes input from row a which is make of car
combo box 2 takes input from row b which is year of car fom 1980 and up
combo box 3 takes input from row c which model of car
combo box takes input from row d which is engine size
there are duplicates in each row.  i would not like the duplicates.  for ex. car manufacturers will make the same car for decades with the same engine.  also if it picks acura, 98, the next combo box gives me choices for all the makes in the row including, bmw, cadillac etc.  same problem with eng size.  i would like after it finds a match to give me the data in row e which is filter # in cell a1 of C:\Users\Owner\Desktop\Real Indy\Sheet1.xlsx
my database is saved as C:\Users\Owner\Desktop\Real Indy\Filter.xlsx
i know i need an index w/ match, i believe.  my code for the combo boxes are very simple.  it is the 1st combo box i have ever made.  thank you very much.  


Private Sub ComboBox1_Change()
 
End Sub
 
Private Sub ComboBox2_Change()
 
End Sub
 
Private Sub ComboBox3_Change()
 
End Sub
 
Private Sub ComboBox4_Change()
 
End Sub
 
Private Sub Label1_Click()
Worksheets("Sheet1").Range("a1") = Vehicle_Type.ComboBox1.Value
Worksheets("Sheet1").Range("a2") = Vehicle_Type.ComboBox2.Value
Worksheets("Sheet1").Range("a3") = Vehicle_Type.ComboBox3.Value
Worksheets("Sheet1").Range("a4") = Vehicle_Type.ComboBox4.Value
Vehicle_Type.Hide
End Sub
 
Private Sub Label2_Click()
Vehicle_Type.Hide
End Sub
 
Private Sub UserForm_Click()
 
End Sub

Open in new window

temp.xlsx
0
Comment
Question by:dave5m
  • 14
  • 9
23 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 24278276
dave5m,
Please upload your file with all the VBA,  including UserForms / ComboBoxes etc.
Patrick
0
 

Author Comment

by:dave5m
ID: 24278501
i think this is it.  the only big thing i did in row source i made it say =[Book1.xlsx]Filter!A:A then BB for the second combo box, etc.  
Book1.xlsx
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24281212
I'm afraid your latest file has no ComboBoxes in it. It seems you have many files which are incomplete. Please find the complete file and upload it here.
Patrick
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dave5m
ID: 24281993
sorry

Book1.xlsx
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24282047
Nope, not got the right.file yet.
0
 

Author Comment

by:dave5m
ID: 24282717
open up baystation.xls, have it saved under forms as vehicle_type.
BAYSTATION.xls
Book1.xlsx
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24283442
dave5m,
If you want to populate a list with unique items only it's useful to assemble the unique items by using a Collection. The way it can be done is in the attached file. Having done that you can use it to populate other objects wherever they are needed.
Hope that helps
Patrick

unique-items-01.xls
0
 

Author Comment

by:dave5m
ID: 24283616
WOW BUT NOW HOW WOULD I DO IT SO WHEN THEY PICK ACURA ONLY THE ACURA MAKES POP UP NOT THE BMW OR AUDI.  OR SOME ACURAS ARE NOT MADE EVERY YEAR SO WHEN THEY PICK 2002 ACURA, INTEGRA STILL POPS UP ON THE COMBO BOX EVEN THOUGH ACURA STOPPED MAKING IT IN 2001.  DO I HAVE TO HAVE A SEPERATE SHEET FOR EVERY YEAR OF EVERY MAKE AND THAN WRITE 1000'S OF IF THEN STATEMENTS IN THE COMBO BOX?? I HOPE NOT.  
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24283823
Insert a new column that combines the cars and the years of manufacture and then create the unique list from that.
0
 

Author Comment

by:dave5m
ID: 24286951
ok i put the code in so only uniques pop up.   but if i pick acura in the first box and then 98 in the second box all the makes for audi bmw's and buick's will still pop up unless i make seperate sheets for each year make model and even engine size? i will end up with 10s of thousands of sheets? than i will have to make if statements in the 2nd 3rd and 4th combo boxes?
 i looked at the pennzoil.com  website and they have this link   http://www.pureoil.com/pennzoilfilter/default_new.aspx and thier dropdown boxes actually go make model, year, so if you have a  acura integra 01 it does not give me the year 02 and up because acura stopped making the integra in 01 but with my drop down list it does.  i will also have these problems with engine sizes.  
 i figured out how to get the filter #.  i can put this code into my first sheet after i get the combo boxes to work.
=IF(ISNA(MATCH(A1&A2&A3&A4,B1:B30000&C1:C30000&D1:D30000&E1:E30000,0)),"No Match",INDEX(F:F,MATCH(A1&A2&A3&A4,B1:B30000&C1:C30000&D1:D30000&E1:E30000,0))
but this must be ctrl/shift/entered,
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24287498
dave5m,
The trouble is I really don't know what you are really wanting to do. If you had started off your question along the lines of - "My ultimate objective is to be able to select from a list the make of a car and from another list the year of the car -  and then be told the oil filter for that car" or whatever it is that you are wanting to achieve then I could have started from the beginning. As it is I haven't a clue what the objective is.
If you feel like forgetting for a moment all of your work to date and just explain in detail what the objective is then I am sure I could help far more constructively. To give you an idea, I cannot remember ever having given someone a partial solution - as I have with you - and I have answered 1000s of questions here!
I realise it's frustrating for you but I'm sure it will help us get there much faster - wherever 'there' is.
Patrick
0
 

Author Comment

by:dave5m
ID: 24287740
i'm sorry, that is my exact objective.  "My ultimate objective is to be able to select from a list the make of a car and from another list the year of the car -  and then be told the oil filter for that carb" but, i believe and i could be wrong, that i would have to explain the whole prosess.  i thought i would have to explain that i want each combo box filtered with the options narrowing down.  now, i can get the filter # with the above code i got, and with the code you gave me yesterday i can get only unique items, thank you (although my friend said he thinks there is a tab in VB that will also give me the unique items in the combo boxes but i did not find it) BUT I NEED THE COMBO BOXES TO FILTER OUT THINGS THAT DO NOT APPLY.    for example when i pick acura in the first combo box every other manufacturers make comes up.  i really tried my hardest to explain as simply as possible and if i am frustrated i know you just want to strangle me .  Again i'm sorry.  Again my main objective is now  I NEED THE COMBO BOXES TO FILTER OUT THINGS THAT DO NOT APPLY. can you still help me please?  
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24287763
dave5m,
I'm trying to get you to forget your ComboBoxes for a moment and just concentrate on what you want - not on how to achieve it, as that's my challenge !
I believe I'm now clearer on what's wanted - but don't hold your breath as I need to start from scratch - so I may well be back with more questions.
Patrick
0
 

Author Comment

by:dave5m
ID: 24287772
thgank you very much, take your time i have alotta data to input meanwhile.  
0
 

Author Comment

by:dave5m
ID: 24287788
by the way if you know a betteer way than combo boxes that's fine, but i'm making this program for people who cant spell, so if they get a buick rendezvous that will take forever, i'm not sure if i spelled that right, but if there is an easier way other than combo boxes that's perfectly fine (a friend sugested HYperlinks, ETC, he also said to look up "Filter in PLace" but i found nothing.  he told me how to go to it in excel 03 but i cant find it in 07.  honestly i do not know what this is or if this is what i am looking for?) thank you.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24288175
dave5m,
I believe I do know a better way to do it. It will take me a bit of effort but as soon as I have something of use I will let you see where I've got to. I have already made some progress with ListBoxes, the item-selected will then determine the contents of the next ListBox and so on. Finally the combination of choices will enable a search for the appropriate oil and air filter - which will be shown in the UserForm.
It should all be doable, but assembling the dependant macros just takes a little time - particularly when I'm doing other things at the same time.
I'll be back tomorrow.
Patrick
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 24289345
dave5m,
OK, here you go. It's all in the attached file. Press the button to find the Oil and Air filter for the chosen Make, Model, Year and Engine. The macros are below - hope you now understand why I wanted you to concentrate on what you wanted rather than how to achieve it.
Hope it does what you wanted - and a bit more.
Patrick
ps. I suggest that once you have completed the data preparation side of things that you upload that complete file so that I can get it to work with the way in which I have set up this workbook - unless you feel confident to do it yourself. The columns with the red font should not be changed and the complete column order of the worksheet should not be changed.

Option Explicit
Public make As String
Public model As String
Public yom As String
Public engine As String
 
Private Sub UserForm_Initialize()
'initialise Userform1 and populate ListBox1 with all the Makes of the cars
Dim coll As New Collection
Dim rng As Range
Dim celle As Range
Dim str1 As String
Dim str2 As String
Dim rowe As Long
Dim i As Long
 
rowe = 2
str1 = "B"
str2 = "B"
With Sheets("Filter")
    Set rng = Range(.Cells(rowe, str1), .Cells(.Cells.Rows.Count, str2).End(xlUp))
End With
 
For Each celle In rng
    If celle <> "" And UCase(celle) <> "OTHER" Then
        On Error Resume Next
        coll.Add celle, celle
    End If
Next celle
 
UserForm1.ListBox2.Clear
UserForm1.ListBox3.Clear
UserForm1.ListBox4.Clear
UserForm1.TextBox1.Text = ""
UserForm1.TextBox2.Text = ""
For i = 1 To coll.Count
    UserForm1.ListBox1.AddItem coll(i)
Next i
 
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'identify the Make selected
Dim i As Long
 
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        make = ListBox1.List(i)
    End If
Next i
Call model_m(make)
End Sub
 
Private Sub model_m(make)
'populate ListBox2 with the Models corresponding to the Make selected in ListBox1
Dim coll As New Collection
Dim rng As Range
Dim celle As Range
Dim str1 As String
Dim str2 As String
Dim rowe As Long
Dim i As Long
 
rowe = 2
str1 = "B"
str2 = "B"
With Sheets("Filter")
    Set rng = Range(.Cells(rowe, str1), .Cells(.Cells.Rows.Count, str2).End(xlUp))
End With
 
For Each celle In rng
    If celle = make And celle.Offset(0, 1) <> "" Then
        On Error Resume Next
        coll.Add CStr(celle.Offset(0, 1)), CStr(celle.Offset(0, 1))
    End If
Next celle
 
UserForm1.ListBox2.Clear
UserForm1.ListBox3.Clear
UserForm1.ListBox4.Clear
UserForm1.TextBox1.Text = ""
UserForm1.TextBox2.Text = ""
For i = 1 To coll.Count
    UserForm1.ListBox2.AddItem coll(i)
Next i
 
End Sub
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'identify the Model selected
Dim i As Long
 
For i = 0 To ListBox2.ListCount - 1
    If ListBox2.Selected(i) Then
        model = ListBox2.List(i)
    End If
Next i
'MsgBox model
Call yom_m(model)
End Sub
 
Private Sub yom_m(model)
'populate ListBox3 with the Year Of Make (yom) corresponding to
'the Model selected in ListBox2
Dim coll As New Collection
Dim rng As Range
Dim celle As Range
Dim str1 As String
Dim str2 As String
Dim rowe As Long
Dim i As Long
 
rowe = 2
str1 = "D"
str2 = "D"
With Sheets("Filter")
    Set rng = Range(.Cells(rowe, str1), .Cells(.Cells.Rows.Count, str2).End(xlUp))
End With
 
For Each celle In rng
    If celle = make & model And celle.Offset(0, 1) <> "" Then
        On Error Resume Next
        coll.Add CStr(celle.Offset(0, 1)), CStr(celle.Offset(0, 1))
    End If
Next celle
 
UserForm1.ListBox3.Clear
UserForm1.ListBox4.Clear
UserForm1.TextBox1.Text = ""
UserForm1.TextBox2.Text = ""
For i = 1 To coll.Count
    UserForm1.ListBox3.AddItem coll(i)
Next i
 
End Sub
 
Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'identify the Year Of Make (yom) selected
Dim i As Long
 
For i = 0 To ListBox3.ListCount - 1
    If ListBox3.Selected(i) Then
        yom = ListBox3.List(i)
    End If
Next i
'MsgBox yom
Call engine_m(yom)
End Sub
 
Private Sub engine_m(yom)
'populate ListBox4 with the Engines corresponding to
'the items selected so far
Dim coll As New Collection
Dim rng As Range
Dim celle As Range
Dim str1 As String
Dim str2 As String
Dim rowe As Long
Dim i As Long
 
rowe = 2
str1 = "F"
str2 = "F"
With Sheets("Filter")
    Set rng = Range(.Cells(rowe, str1), .Cells(.Cells.Rows.Count, str2).End(xlUp))
End With
 
For Each celle In rng
    If celle = make & model & yom And celle.Offset(0, 1) <> "" Then
        On Error Resume Next
        coll.Add CStr(celle.Offset(0, 1)), CStr(celle.Offset(0, 1))
    End If
Next celle
 
UserForm1.ListBox4.Clear
UserForm1.TextBox1.Text = ""
UserForm1.TextBox2.Text = ""
For i = 1 To coll.Count
    UserForm1.ListBox4.AddItem coll(i)
Next i
 
End Sub
 
Private Sub ListBox4_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'identify the Engine selected
Dim i As Long
 
For i = 0 To ListBox4.ListCount - 1
    If ListBox4.Selected(i) Then
        engine = ListBox4.List(i)
    End If
Next i
'MsgBox engine
UserForm1.TextBox1.Text = ""
UserForm1.TextBox2.Text = ""
Call filters
End Sub
 
Private Sub filters()
'identify the filters corresponding to the items selected so far
Dim rng As Range
Dim celle As Range
Dim str1 As String
Dim str2 As String
Dim rowe As Long
Dim i As Long
 
rowe = 2
str1 = "A"
str2 = "A"
With Sheets("Filter")
    Set rng = Range(.Cells(rowe, str1), .Cells(.Cells.Rows.Count, str2).End(xlUp))
End With
 
For Each celle In rng
    If celle = make & model & yom & engine Then
        If celle.Offset(0, 7) <> "" Then
            UserForm1.TextBox1.Text = celle.Offset(0, 7)
        Else
            UserForm1.TextBox1.Text = "Not listed"
        End If
        If celle.Offset(0, 8) <> "" Then
            UserForm1.TextBox2.Text = celle.Offset(0, 8)
        Else
            UserForm1.TextBox2.Text = "Not listed"
        End If
    End If
Next celle
 
End Sub
 
Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

Open in new window

car-data-02.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24289365
dave5m,
Just a comment - If you want it smartened-up so that the data can be loaded into the worksheet more easily and with the selections in the ListBoxes sorted in alpha/numeric order then, I believe, that merits a completely new question.
Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24289632
dave5m,
If you would prefer a non-VBA solution then there is a simple way to do it using the ordinary built-in Filter system. It's in the attached file.
Make the selection using the dropdown arrows in row 1.
Patrick

car-data-05-non-VBA.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24289639
dave5m,
If you would prefer a non-VBA solution then there is a simple way to do it using the ordinary built-in Filter system. It's in the attached file - V2 - (all VBA removed)
Make the selection using the dropdown arrows in row 1.
Patrick

car-data-05-non-VBA-V2.xls
0
 

Author Closing Comment

by:dave5m
ID: 31579685
i am so sorry.  i have had a family emergency and could not attend.  i am very sorry for the miscommunication.  i took a quick look at everything and it looks like you spent alot of time on this.  i do not not know how to thank you enough.  
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24342634
dave5m,
I trust the family emergency is now over and that it wasn't too bad after all.
Thanks for the grade. I'm pleased the solution does what you want. You're right, I did spend a bit of time on it, but then I wanted it to look the business as well as behave reliably and I think I achieved that.
Since providing you with that solution I have worked on it some more and come up with a smartened-up edition. It has the following features, which you didn't ask for:
- all the sections are sorted so that the Model, Year and Engine sections are easier to find
- the data worksheet keeps all the data in adjacent columns - so making it easy to copy and paste all your data straight into the worksheet
- a much faster speed of response from the macro
As you are a Premium Service Member, I make no apologies for suggesting that you ask another question with those new points in it. I believe you will like those changes/improvements as they make the whole application significantly better.
Patrick
 
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24342880
dave5m,
I have provided you with a new answer (and file) in your other question:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Visual_Basic_v1.0.5.x/Q_24356965.html#a24342874
The new macro deals with those three issues mentioned in my last comment - above.
Hope you find it much improved.
Patrick
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question