[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2013-11-09
3
Medium Priority
?
496 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 54

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.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
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…

591 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