Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I get a form to lookup data for 3 different controls? (co.
Hi,
I assume the data in held in a table in a worksheet (as this is not stated in your query) so something like this will work: A B C D E F ....... City Age Gender Data London 24 Female Mary Paris 39 Male John London 25 Female Natasha New York 30 Female Jane London 24 Male Mark London 24 Female Nicola Sub TestLookup(Location As String, Age As Integer, Gender As String) Dim rng As Range. Dim i as Long, iLastrow as Long Set rng = Range("A1:I10") iLastrow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastrow If Application.And(rng(i, 1) = Location, rng(i, 2) = Age, rng(i, 3) = Gender) Then ' Get Data End If Next i End Sub Sub Looktest() ' Replace parameters by controls e.g Call TestLookup(ListboxA.value, ListboxB.value,listboxC.Value) Call TestLookup("London", 24, "Female") End Sub HTH "Michael" wrote: Ex. Pull a group of people 24 years of age, from London, that are female. where Control A = City(London), control B = Age(24), Control C = Gender(Female) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I get a form to lookup data for 3 different controls?
Sorry I meant to post in ACCESS Group not Excel.
if you can still help, that would be great. Here is a more detailed of what I'm trying to do... I have a form with 3 list box controls. (i.e., listbox 1 lists all products, listbox 2 list Month and listbox 3 displays YEAR. What I would like to do is to display data base on a selection per list box. For example...if I select Laptop in Product Listbox, it will display all entry with the item "Laptop". And if I then select "2004" in YEAR Listbox, it should display all "LAPTOP" where year YEAR is "2004". "Toppers" wrote: Hi, I assume the data in held in a table in a worksheet (as this is not stated in your query) so something like this will work: A B C D E F ....... City Age Gender Data London 24 Female Mary Paris 39 Male John London 25 Female Natasha New York 30 Female Jane London 24 Male Mark London 24 Female Nicola Sub TestLookup(Location As String, Age As Integer, Gender As String) Dim rng As Range. Dim i as Long, iLastrow as Long Set rng = Range("A1:I10") iLastrow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastrow If Application.And(rng(i, 1) = Location, rng(i, 2) = Age, rng(i, 3) = Gender) Then ' Get Data End If Next i End Sub Sub Looktest() ' Replace parameters by controls e.g Call TestLookup(ListboxA.value, ListboxB.value,listboxC.Value) Call TestLookup("London", 24, "Female") End Sub HTH "Michael" wrote: Ex. Pull a group of people 24 years of age, from London, that are female. where Control A = City(London), control B = Age(24), Control C = Gender(Female) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I get a form to lookup data for 3 different controls?
If you are using Access to retrieve your data then I don't have sufficient
knowledge to help you ... sorry. "Michael" wrote: Sorry I meant to post in ACCESS Group not Excel. if you can still help, that would be great. Here is a more detailed of what I'm trying to do... I have a form with 3 list box controls. (i.e., listbox 1 lists all products, listbox 2 list Month and listbox 3 displays YEAR. What I would like to do is to display data base on a selection per list box. For example...if I select Laptop in Product Listbox, it will display all entry with the item "Laptop". And if I then select "2004" in YEAR Listbox, it should display all "LAPTOP" where year YEAR is "2004". "Toppers" wrote: Hi, I assume the data in held in a table in a worksheet (as this is not stated in your query) so something like this will work: A B C D E F ....... City Age Gender Data London 24 Female Mary Paris 39 Male John London 25 Female Natasha New York 30 Female Jane London 24 Male Mark London 24 Female Nicola Sub TestLookup(Location As String, Age As Integer, Gender As String) Dim rng As Range. Dim i as Long, iLastrow as Long Set rng = Range("A1:I10") iLastrow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastrow If Application.And(rng(i, 1) = Location, rng(i, 2) = Age, rng(i, 3) = Gender) Then ' Get Data End If Next i End Sub Sub Looktest() ' Replace parameters by controls e.g Call TestLookup(ListboxA.value, ListboxB.value,listboxC.Value) Call TestLookup("London", 24, "Female") End Sub HTH "Michael" wrote: Ex. Pull a group of people 24 years of age, from London, that are female. where Control A = City(London), control B = Age(24), Control C = Gender(Female) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveX Controls vs Form Controls | Excel Discussion (Misc queries) | |||
Form controls | Excel Programming | |||
about more controls of a form | Excel Programming | |||
How to query the controls in form using vb | Excel Programming | |||
User Form Controls | Excel Programming |