Excel VBA macro stops after Autofilter changes filter state

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

wcufflin

Thread Starter
Joined
Dec 18, 2010
Messages
2
Applications:
Excel 2007, SP1 & MS Visual Basic

Background:
I use several macros in an Excel worksheet, each of which is designed to filter data in my AutoFiltered list based on some criteria.

Problem:
When a line of code in my macro CHANGES THE FILTERED STATE OF THE AUTOFILTERED LIST, the macro stops processing further lines of code. There is no error message generated when the macro stops.

To illustrate the logic of the problem:

SCENARIO 1
1. Macro turns on AutoFilter for the list.
2. Macro successfully applies a new criteria filter to the list. (i.e. THE AUTOFILTER'S FILTER STATE HAS CHANGED.)
3. Macro STOPS, failing to execute further lines of code in the macro.

Private Sub cmdShowLateStatus_Click()
With ActiveSheet
(1.) .Range("A15").AutoFilter
(2.) .Range("QuickViewOrderTable").AutoFilter Field:=11, Criteria1:="=y" (3.)
End With
' Scroll to top of list.
Range("P15").Select
ActiveCell.Offset(1, 6).Select
End Sub



SCENARIO 2
NOTE: Before the below macro starts, AutoFilter is already on, and the list is already filtered based on a certain criteria.
1. Macro successfully clears the filter in the list, but leaves AutoFilter on. (i.e. THE AUTOFILTER'S FILTER STATE HAS CHANGED.)
2. Macro STOPS, failing to execute further lines of code in the macro

Private Sub cmdShow_All_Orders_Click()
' If AutoFilter is ON, then clear any filters.
If ActiveSheet.AutoFilterMode Then
If ActiveSheet.FilterMode Then
(1.) ActiveSheet.ShowAllData (2.)
End If
End If
' Scroll to top of list.
Range("P15").Select
ActiveCell.Offset(1, 6).Select
End Sub



I opened the macros in Visual Basic editor to step through them, using F8. This is what I observed:
Once execution proceeds to the end of the command line that was responsible for changing the AutoFilter's filter state, the insertion point moves back to the start of this same command line. No line of code remains highlighted yellow, just a flashing insertion point. At this time, the macro has all but stopped executing code.

Any help in solving this mysterious macro "stopping" behaviour would be most appreciated!

- Bill
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
All the mcrodoes is select the offeste 1 row down 6 colkumns to the right, thtáts what you tell it to do.
It doesn'tdo anything after the check.
Is a sample possible?
 

wcufflin

Thread Starter
Joined
Dec 18, 2010
Messages
2
Keebellah,

What samples are you looking for besides the two samples of code I gave? Do you need screenshots of the worksheet?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Code doesn't tell me anything about the sheet layout.
It tells more than trying to figure out what the sheet could look like.

Further more what is the trigger threshold to send the mail.

Instead of a screenshot a simple sample sheet with let's say three to five rows of (ficticious) data helps.

I'll be back on-line after the January 3d.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top