Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel 2003 VBA Programmer's Reference
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 27th, 2010, 02: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 02:46 AM. Reason: want to add attachment but no option
Reply With Quote
  #2 (permalink)  
Old October 27th, 2010, 03:52 AM
Friend of Wrox
Points: 689, Level: 9
Points: 689, Level: 9 Points: 689, Level: 9 Points: 689, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #3 (permalink)  
Old October 27th, 2010, 03: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.
Reply With Quote
  #4 (permalink)  
Old October 27th, 2010, 04:09 AM
Friend of Wrox
Points: 689, Level: 9
Points: 689, Level: 9 Points: 689, Level: 9 Points: 689, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #5 (permalink)  
Old October 27th, 2010, 04: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 04:36 AM. Reason: To Add one more query
Reply With Quote
  #6 (permalink)  
Old October 27th, 2010, 10:34 AM
Friend of Wrox
Points: 689, Level: 9
Points: 689, Level: 9 Points: 689, Level: 9 Points: 689, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #7 (permalink)  
Old October 27th, 2010, 10:41 AM
Friend of Wrox
Points: 689, Level: 9
Points: 689, Level: 9 Points: 689, Level: 9 Points: 689, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #8 (permalink)  
Old October 28th, 2010, 01: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.
Reply With Quote
  #9 (permalink)  
Old October 28th, 2010, 01:59 AM
Friend of Wrox
Points: 689, Level: 9
Points: 689, Level: 9 Points: 689, Level: 9 Points: 689, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
The Following User Says Thank You to mtranchi For This Useful Post:
aalokwar (October 28th, 2010)
  #10 (permalink)  
Old October 28th, 2010, 03:41 AM
Friend of Wrox
Points: 689, Level: 9
Points: 689, Level: 9 Points: 689, Level: 9 Points: 689, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
About listbox selection mode chandra_yvr ASP.NET 2.0 Basics 1 April 3rd, 2007 08:50 AM
How to filter records based on selection of combox method Access 1 June 30th, 2005 04:34 AM
listbox selection jon23d Access VBA 1 June 21st, 2005 02: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 08:21 AM



All times are GMT -4. The time now is 02:11 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.