Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to determine if an Excel Table filter is on or off

Posted on 2013-11-09
3
Medium Priority
?
507 Views
Last Modified: 2013-11-09
I have a Command Button to Update a table connected to an Access Query.  Sometimes the table is filtered and sometimes it is not.

Currently my VBA code looks like this:

Sheets("MeditechData").Select
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("C1").Select
ActiveWorkbook.RefreshAll

If the filter has been turned on this works fine.  However, if the table is not filtered, I get the error :

ShowAllData method of Worksheet class failed.

Is there a way I can set up a check to see if the table is filtered or not?

Thanks

Glen
0
Comment
Question by:GPSPOW
3 Comments
 
LVL 55

Expert Comment

by:Rgonzo1971
ID: 39636160
Hi,

pls try

If Not ActiveSheet.AutoFilter Is Nothing Then
 ' your code
End If

Open in new window

Regards
0
 
LVL 2

Accepted Solution

by:
mensmaximus earned 2000 total points
ID: 39636172
Try something like

If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("c1").AutoFilter
End If

Open in new window

0
 

Author Closing Comment

by:GPSPOW
ID: 39636198
Thanks

I added your lines to my code and it worked.

glen
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This is a comprehensive review of a bundled Toolkit designed for use by IT Professionals and End Users to help Microsoft Outlook fans manipulate Outlook files and repair some common problems. Enjoy...
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question