?
Solved

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

Posted on 2009-04-26
16
Medium Priority
?
329 Views
Last Modified: 2013-12-26
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.  
0
Comment
Question by:dave5m
  • 8
  • 6
  • 2
16 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 24241983
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 ?
0
 

Author Comment

by:dave5m
ID: 24244672
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.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24248008
Can you attach the sheet to this question !
0
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!

 

Author Comment

by:dave5m
ID: 24250369
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
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24250380
In which combobox ? Do you create one in the excel sheet or in another program that reads this excel file ?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24250396
When you perform this: (menu: data - filter - autofilter)
Is this what you are looking for ?
0
 

Author Comment

by:dave5m
ID: 24250512
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.  
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24250520
Can you post the code that you now use for filling the combobox(es) !
0
 

Author Comment

by:dave5m
ID: 24250603
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
0
 

Author Comment

by:dave5m
ID: 24268961
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.
0
 

Author Comment

by:dave5m
ID: 24273547
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.
0
 

Author Comment

by:dave5m
ID: 24274358
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
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24293040
Are you loading the data from excel into a .net-application  or in a vb6-applicaton or are you programming in excel ?
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 24342874
dave5m,
I see that this is the same question but I can and will give you a different answer as outlined in the other question. That will mean you don't need to ask another question to get my 'new improved' edition with those features you didn't ask for - namely:
- all the sections are sorted so that the Model, Year and Engine 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
It's all in the attached file.
Patrick

car-data-06.xls
0
 

Author Closing Comment

by:dave5m
ID: 31574773
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.  
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24346208
dave5m - Thanks for the grade. Let me know in due course if there are other features you would like included. - Patrick
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

840 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