Link to home
Start Free TrialLog in
Avatar of dave5m
dave5m

asked on

getting input from four combo boxes in one user form and not posting duplicates.

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
it takes it from C:\Users\Owner\Desktop\Real Indy\Filter.xlsx and post it in row A cell 1,2,3,4 repsectively in C:\Users\Owner\Desktop\Real Indy\Sheet1.xlsx.  i have each make written at least 150 times so Acura comes first and it says Acura 150 times in clomn A then Integra is repeated several times since it was made for many years and same problem with engine sizes so they repeat in the combo boxes.  How do i stop that?  and then when the user chooses 99 acura Cl w/ the 2.3L engine  in Colum E i have the oil filter number and would like to take that input from row E Filter.xlsx and paste in in A5 Sheet1.  i have very little knowledge of macros so if the whole macro is written that would be great.  
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Can you explain it a little bit better. (and post the file here).
You have an excel-sheet with cars, mark ,...
Now you want some kind of filtering ?
Avatar of dave5m
dave5m

ASKER

Sorry.
In page C:\Users\Owner\Desktop\Real Indy\Filter.xlsx ;
Column A has make of cars.  The first 150 entries are Acura, the next 150 entries are Audi folowed by Buick, etc.  So i have over 3000 available entries repeated in the combo box when ther are no more than 20 different makes.
Column B has year.  Car manufacturers produce about 10 different models a year so i have 2009 ten times/ manufacturer.  GM produces 20 different makes.  so i only need the year 1980-2009 once but they appear hundreds of times.
Column C has model.  Some models are made for decades, ex Civic, so i have civic from 29 times, for each year from 1980 until 2009.
Column D has engine size.  Some cars have two or more differnt Engine sizes available the same year and some times that same engine is made for a decade.  Ex, Grand Cherkees can have a 6 cylinder or 8 for the past 10 years so those two, same engine sizes pop up in my combo box 10 times.  
and yes after i fix the duplictae problem for the combo boxes i would like it to match all 4 columns and when it finds a match (or filter) to give me the input from column E (which is the oil filter number) on a differnent page; C:\Users\Owner\Desktop\Real Indy\Sheet1.xlsx. thank you.
Can you attach the sheet to this question !
Avatar of dave5m

ASKER

i'm sorry.  i only got half way throgh Buick but you should definitely get the idea from thge data i have so far.  thank you.
so i want the duplicates not to pop up 150 times in the comboi box.  and i want it to find the match of the make/year/model/engine size and return the oil filter # on another sheet.  thank you.  
Book1.xlsx
In which combobox ? Do you create one in the excel sheet or in another program that reads this excel file ?
When you perform this: (menu: data - filter - autofilter)
Is this what you are looking for ?
Avatar of dave5m

ASKER

i am very new to this.  I made one user form and inserted 4 combo boxes in it.  THE DATA IS IN Real Indy\Filter.xlsx  Is that good? first combo box asks model and pastes the answer in cell A1 Real Indy\Sheet1.xlsx the next combo box asks year and pastes answer in A2 etc.  i really do not need this data pasted here but i do not know how to make one combo box ask make, when that one gets the make to filter out only the years i have available for make and than when it gets make and years, for example if the user piuck Acura, 90, the 3rd combo box gives a list of all the models from acura to buick.  i created a combo box in Microsoft visual Basic.  
Can you post the code that you now use for filling the combobox(es) !
Avatar of dave5m

ASKER

ah, it's really simple, but i am really proud for my first como box on my own?
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
Avatar of dave5m

ASKER

i could just use an excell code that i can plug into cell f4 of C:\Users\Owner\Desktop\Real Indy\Sheet1.xlsx that can match the 4 columns too.  we were flying the other day and than we just stopped.  are you still there?  please.  help.
Avatar of dave5m

ASKER

somebody gave me this:
=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, not just entered.
which i converted to work with my macro by changing cell#'s around and if no match comes up it says "no match" so i have an if then statement in the macro which says if the cell staes no match to do something else.
Avatar of dave5m

ASKER

but the combo boxes still have duplicates and if i pick honda they give me every make like caprice and taurus.  i think i can do stop it by putiing each make on one sheet, each model on one sheet, etc, and putting alot of if then statements in my combobox but that will take years.  i think
Are you loading the data from excel into a .net-application  or in a vb6-applicaton or are you programming in excel ?
ASKER CERTIFIED SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dave5m

ASKER

yes but i asked this question a week before i reposted it and you replied.  thank you very much.  i am still away and will be able to look at this next week so keep an eye on me if i repost any questions for this.  thank you.  
dave5m - Thanks for the grade. Let me know in due course if there are other features you would like included. - Patrick