Filter worksheet as per validation listbox selection

aalokwar · October 27th, 2010, 01:40 AM

I want to filter worksheet data as per the value selected in validation Listbox.

mtranchi · October 27th, 2010, 02:52 AM

can you give me some more details? the code that you're trying to write, etc

aalokwar · October 27th, 2010, 02:58 AM

I have copied data from excel sheet.

Country
Nepal <- Select a country from the Validation Listbox.

Country aa bb cc
India 34 56 78
Nepal 45 67 86
US 34 44 45
India 23 33 34
Nepal 44 56 66
US 77 77 88
India 65 56 77
Nepal 33 34 45
India 88 87 77
Nepal 33 57 78

Above lists should get filtered as per selection made in the Validation listbox shown on Top row.

mtranchi · October 27th, 2010, 03:09 AM

the code would be something like this

Code:

Sub FilterData()

    Sheets("sheet1").Range("dataset").AutoFilter
    ActiveSheet.Range("$A$3:$C$13").AutoFilter Field:=1, Criteria1:=textbox1.Text

End Sub

where textbox1 is the name of the text box you're putting the country name into, and "dataset" is the range where the countries are listed

aalokwar · October 27th, 2010, 03:32 AM

Thanks mtranchi,
Its working fine but I need one more help please.
How, I can run macro automatically when value is changed in Validation listbox, and also it should display entire records when ALL is selected from the listbox.
Thanks in Advance.
Aalok

mtranchi · October 27th, 2010, 09:34 AM

when you're in design mode, double click the list box and it should bring you to the listbox change event. put the code in there.
Should look something like this

Code:

Sub Listbox1_Change() 'Where Listbox is whatever you named it
    Sheets("sheet1").Range("dataset").AutoFilter
    ActiveSheet.Range("$A$3:$C$13").AutoFilter Field:=1, Criteria1:=textbox1.Text
End Sub

take care

mtranchi · October 27th, 2010, 09:41 AM

Oh, to make it show all, just use the autofilter again without any parameters

Code:

ActiveSheet.Range("$A$3:$C$13").AutoFilter
'something like that, but the proper sheet and range

i should say though that it sounds like you maybe reinventing the wheel. The autofilter function does exactly what you're doing and no need to write code.
Don't know what version of excel you have, pre 2007 i think autofilter is under Data. 2007 and 2010 it's on the Data tab.
Hope that helps
take care

aalokwar · October 28th, 2010, 12:22 AM

Thanks dear for giving prompt support.
I could solve the problem.

mtranchi · October 28th, 2010, 12:59 AM

Never thought of trolling the VBA forums for cuties. I am very much attracted to females with good minds, math skills and analytical abilities a plus, lol

mtranchi · October 28th, 2010, 02:41 AM

Jeez, guess I'm gonna sound pathetic here, but...
Yes, ugh, I'm single at the moment. It seems like every girl i come across hates math or sucks at it.
I guarantee this:
You will find me attractive physically.
I have a sense of humor you will love.
I put the toilet seat down (only if there's a woman in the house though).
I've stood tall against virtually all threats i've had to deal with in life. No, I'm not the baddest ass, but I'll scare away most with a mere look.
I am trainable.