+ Reply to Thread
Results 1 to 5 of 5

Autofilter Criteria in Msgbox

  1. #1
    Registered User
    Join Date
    02-22-2011
    Location
    Staffordshire
    MS-Off Ver
    Excel 2007
    Posts
    16

    Autofilter Criteria in Msgbox

    Hi guys

    Thanks in advance for any help.

    I'm trying to select the header of any filtered table and output the Criteria as a list in a msgbox to show whats filtered and by what.

    I found a function that does a similar thing but it involves showing the values above the filters.

    I was hoping to basically highlight a header and run a macro with a key combination that pops up Msgbox showing filters being used.

    I've wasted a morning trying to butcher the function code and I'm either having a dumb day or just plain losing the plot.

    Can any one help?

    Function Code found

    Please Login or Register  to view this content.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Autofilter Criteria in Msgbox

    if that function does what you need (I suspect it was written before xl 2007) then all you need in your sub is
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    02-22-2011
    Location
    Staffordshire
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Autofilter Criteria in Msgbox

    Thanks for the response JosephP.

    I did try along that line but it would only display the first filter and its criteria. I wanted to create a method of say opening a sheet, selecting the top left header cell, run the macro and a message box appears showing the Headers filtered and with what criteria.

    I've cobbled together the below code which kind of works except it falls over if more than two criteria are selected in a filter or if the Date criteria is say a whole year and not a single date. On the plus side it does display the filter criteria for multiple filters and it will show if 1 or 2 filters are set in a filter.

    Please take a look and feel free to *** apart or add too :-)

    Please Login or Register  to view this content.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Autofilter Criteria in Msgbox

    untested but perhaps
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-22-2011
    Location
    Staffordshire
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Autofilter Criteria in Msgbox

    Thanks thats brilliant and works ok apart for two small parts.

    If a single filter is applied it doesnt show you the value.
    I just moved the "sTemp = .Criteria1" in the function to before the Select Case and it sorted this problem.

    The second is it still falls over if a Date is selected say by the summed Year value and I will try to solve this issue but thank you for the code its great.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1