Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel 2003 VBA Programmer's Reference
|
BOOK: Excel 2003 VBA Programmer's Reference
This is the forum to discuss the Wrox book Excel 2003 VBA Programmer's Reference by Paul T. Kimmel, Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg, Brian Patterson; ISBN: 9780764556609
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2003 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 27th, 2010, 01:40 AM
Registered User
 
Join Date: Oct 2010
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Post Filter worksheet as per validation listbox selection

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

Last edited by aalokwar; October 27th, 2010 at 01:46 AM.. Reason: want to add attachment but no option
 
Old October 27th, 2010, 02:52 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default more details

can you give me some more details? the code that you're trying to write, etc
 
Old October 27th, 2010, 02:58 AM
Registered User
 
Join Date: Oct 2010
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Smile Complete details: Filter worksheet as per validation listbox selection

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.
 
Old October 27th, 2010, 03:09 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old October 27th, 2010, 03:32 AM
Registered User
 
Join Date: Oct 2010
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Thumbs up Thanks

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

Last edited by aalokwar; October 27th, 2010 at 03:36 AM.. Reason: To Add one more query
 
Old October 27th, 2010, 09:34 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old October 27th, 2010, 09:41 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old October 28th, 2010, 12:22 AM
Registered User
 
Join Date: Oct 2010
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default Thanks

Thanks dear for giving prompt support.
I could solve the problem.
 
Old October 28th, 2010, 12:59 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default Dear?

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
The Following User Says Thank You to mtranchi For This Useful Post:
aalokwar (October 28th, 2010)
 
Old October 28th, 2010, 02:41 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
About listbox selection mode chandra_yvr ASP.NET 2.0 Basics 1 April 3rd, 2007 07:50 AM
How to filter records based on selection of combox method Access 1 June 30th, 2005 03:34 AM
listbox selection jon23d Access VBA 1 June 21st, 2005 01:35 PM
ListBox Filter dgold Access 3 March 30th, 2005 06:00 PM
how to filter out unwanted selection in rs paging gilgalbiblewheel Classic ASP Databases 1 October 18th, 2004 07:21 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.