dave5m
asked on
matching 4 combo boxes
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\Rea
my database is saved as C:\Users\Owner\Desktop\Rea
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.
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\Rea
my database is saved as C:\Users\Owner\Desktop\Rea
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
ASKER
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
Book1.xlsx
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
Patrick
ASKER
Nope, not got the right.file yet.
ASKER
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
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
ASKER
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.
Insert a new column that combines the cars and the years of manufacture and then create the unique list from that.
ASKER
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
but this must be ctrl/shift/entered,
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
but this must be ctrl/shift/entered,
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
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
ASKER
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?
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
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
ASKER
thgank you very much, take your time i have alotta data to input meanwhile.
ASKER
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
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
ASKER
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.
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
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
dave5m,
I have provided you with a new answer (and file) in your other question:
http://www.experts-exchang
The new macro deals with those three issues mentioned in my last comment - above.
Hope you find it much improved.
Patrick
I have provided you with a new answer (and file) in your other question:
http://www.experts-exchang
The new macro deals with those three issues mentioned in my last comment - above.
Hope you find it much improved.
Patrick
Please upload your file with all the VBA, including UserForms / ComboBoxes etc.
Patrick