CODEWITHSUNDEEP
excelvba
Vegard
Vegard

Reputation: 4917

Detect Autofilter changes

I am looking to detect any changes in an autofilter on a specific table, for the purpose of forcing a UDF that does some simple arithmetic on the table entries that are currently visible to update its result. Making the UDF volatile has had no impact so far, but it doesn't target the table directly either.

This solution (https://stackoverflow.com/a/15906275/4604845) does not work for me, probably because I have only manual calculation.

My a-little-too-dirty workaround is Workbook_SelectionChange, and while the UDF doesn't kill resource consumption this way, I'd rather avoid it in case the data grows larger. Also I have some users that are complete novices in terms of using computers, and I have trouble being confident that I can get all of them to understand that they need to click something else after updating the autofilter for the result to be correct.

Any ideas? I have tried Workbook_Change and Workbook_Calculate but none of them are triggered (or I can't figure out how to trigger them) by the autofilter changes.

Upvotes: 1

Views: 2205

Answers (2)

EEM
EEM

Reputation: 6659

How to trigger Worksheet Calculation following AutoFilter changes while in Manual Calculation

As we know changes to the AutoFilter selection cannot be automatically detected, as these changes do not trigger any Workbook Event or Worksheet Event. Therefore, only option available is to have the user triggering worksheet calculations with an action i.e. Cell Selection Change, Right Click, Double Click, etc; or just by pressing [F9]; which is the preferable action as there is nothing else involved and it's the way it's designed to work.

Nevertheless, at the user request, I'm providing this VBA code that although needs to be initiated by user's action, this action can be done immediately after the AutoFilter change is selected, just by Double Clicking.

The DoubleClick can be unrestricted (double clicking any cell in the worksheet) by using this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal rTrg As Range, blCancel As Boolean)
    rTrg.Worksheet.Calculate
End Sub

or setting up to three types of restrictions:

  1. Any cell of the Table

  2. The Header of the Table

  3. The Body of the Table

Use this code to restrict the DoubleClick area: Currently the code is set to restriction type 1, use variable bRType to change it to the preferred type. This code assumed the name of the Table is Table1 (change as required)

Private Sub Worksheet_BeforeDoubleClick(ByVal rTrg As Range, blCancel As Boolean)
Dim ObjLst As ListObject, rTbl As Range, bRType As Byte

    Rem Set Restriction Type
    Rem 1: DoubleCliking any cell of the Table - Default
    Rem 2: DoubleCliking any cell of the Table Header
    Rem 3: DoubleCliking any cell of the Table Body
    bRType = 1

    With rTrg
        Set ObjLst = .Worksheet.ListObjects("Table1")

        Select Case bRType
        Case 2:     Set rTbl = ObjLst.HeaderRowRange
        Case 3:     Set rTbl = ObjLst.DataBodyRange
        Case Else:  Set rTbl = ObjLst.Range
        End Select

        If Not (Intersect(.Cells, rTbl) Is Nothing) Then
            .Worksheet.Calculate
            blCancel = True
        End If
    End With
End Sub

Both procedures are Worksheet Events, therefore ensure that the one that you decide to implement goes into the module of the worksheet holding the Table. (do not change the name of the procedures)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Even if you have no other formulas on the worksheet, if you include a Subtotal() formula somewhere on the sheet referencing the table, the Subtotal() will recalculate every time the autofilter is changed.

You can use this to trigger a Calculate() event macro.

EDIT#1:

Say we have an AutoFilter set on column A of a sheet named data. Sheet data also contains many other formulas. If we use the Calculate() event in the data worksheet, we will get fires any time any of these formulas re-calculate.

We create a new worksheet called trigger. This new worksheet is comletely empty except for a single cell that contains:

=SUBTOTAL(3,data!A1:A20)

It is in the trigger worksheet that we place the Calculate() event macro.

Now if we are using the data worksheet, we can make arbitrary changes and perform various recalculation and nothing fires, but if we change the AutoFilter, the event macro on trigger will see the change and fire!

Upvotes: 2

Related Questions