1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA macro stops after Autofilter changes filter state

Discussion in 'Business Applications' started by wcufflin, Dec 18, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. wcufflin

    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
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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?
     
  3. wcufflin

    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?
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/969335

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice