SOLVED

Testing to Determine IF a Filter is active

%3CLINGO-SUB%20id%3D%22lingo-sub-2650967%22%20slang%3D%22en-US%22%3ETesting%20to%20Determine%20IF%20a%20Filter%20is%20active%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2650967%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20working%20on%20a%20spreadsheet%20that%20consists%20of%20two%20sheets%20-%20a%20%22Transaction%20Log%22%20that%20contains%20a%20list%20of%20expense%20records.%26nbsp%3B%20The%20second%20sheet%20is%20titled%20%22New%20Summary%22%20and%20is%20basically%20a%20%22dashboard%22%20that%20displayed%20summarized%20totals.%26nbsp%3B%20%26nbsp%3BThe%20%22Dashboard%22%20includes%20a%20number%20of%20buttons%20-%20each%20of%20which%20executes%20a%20Macro%20that%20places%20a%20filter%20on%20the%20Transaction%20log%20that%20displays%20just%20the%20records%20associated%20with%20the%20%22drill%20down%22%20selected.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20added%20a%20button%20titled%20%22Clear%20Filters%20and%20Return%22%20on%20the%20Transaction%20Log%20that%20fires%20off%20the%20following%20code%20snippet%20-%20which%20clears%20the%20filter%20-%20returns%20the%20user%20to%20the%20%22New%20Summary%22%20sheet.%26nbsp%3B%20%26nbsp%3BThis%20all%20works%20exactly%20as%20I%20want%20it%20to%20EXCEPT%20in%20instances%20that%20the%20%22Clear%20Filters%20and%20Return%22%20button%20is%20selected%20when%20no%20filter%20is%20active%20on%20the%20Transaction%20log.%26nbsp%3B%20%26nbsp%3BExecuting%20this%20block%20of%20code%20when%20no%20filter%20is%20in%20effort%20generates%20a%20%221004%20Run%20Time%20Error%22.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSub%20ClearAndReturn()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B'ActiveSheet.ShowAllData%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSheets(%22New%20Summary%22).Select%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20somebody%20tell%20me%20how%20test%20to%20determine%20IF%20a%20filter%20is%20in%20effort%20prior%20to%20issuing%20the%20%22ActiveSheet.ShowAllData%22%20command%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2650967%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2651292%22%20slang%3D%22en-US%22%3ERe%3A%20Testing%20to%20Determine%20IF%20a%20Filter%20is%20active%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2651292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127761%22%20target%3D%22_blank%22%3E%40SpaceNorman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20If%20ActiveSheet.FilterMode%20Then%0A%20%20%20%20%20%20%20%20ActiveSheet.ShowAllData%0A%20%20%20%20End%20If%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20ActiveSheet.ShowAllData%0A%20%20%20%20On%20Error%20GoTo%200%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm working on a spreadsheet that consists of two sheets - a "Transaction Log" that contains a list of expense records.  The second sheet is titled "New Summary" and is basically a "dashboard" that displayed summarized totals.   The "Dashboard" includes a number of buttons - each of which executes a Macro that places a filter on the Transaction log that displays just the records associated with the "drill down" selected.   

 

I've added a button titled "Clear Filters and Return" on the Transaction Log that fires off the following code snippet - which clears the filter - returns the user to the "New Summary" sheet.   This all works exactly as I want it to EXCEPT in instances that the "Clear Filters and Return" button is selected when no filter is active on the Transaction log.   Executing this block of code when no filter is in effort generates a "1004 Run Time Error".   

 

     Sub ClearAndReturn()
     'ActiveSheet.ShowAllData
     Sheets("New Summary").Select
     End Sub

 

Can somebody tell me how test to determine IF a filter is in effort prior to issuing the "ActiveSheet.ShowAllData" command?  

2 Replies
best response confirmed by SpaceNorman (New Contributor)
Solution

@SpaceNorman 

Try

    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If

or

    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
I went the "If Then" route (Figuring it was better to avoid generating the error in the first place) and it solved my problem! Thank you for the quick feedback! Your help is truly appreciated.