Detect Autofilter changes

Question

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.

The syntax should be `=SUBTOTAL( 3, TableName[Field] )` instead of `=SUBTOTAL( 3, tableName)` and it should recalculate every time the user hits `[F9]` — EEM, Oct 12 '15 at 18:09
Yes, I'm aware of the syntax. Sorry if I was unclear before. The formulas I've devised work just fine in terms of displaying the desired information, I am only struggling with how to make it auto-update within the limitations of my workbook. — Vegard, Oct 13 '15 at 12:54
Yeah, if I opt out of putting `EnableCalculation = False`. I am aware that this would update the sheet, but it's not as automatic as I would prefer... and the overhead is massive, seeing as how I have in the area of some 10k+ functions on that particular sheet. But it is workable even with the overhead, so it remains as one of several possible backup solutions. — Vegard, Oct 13 '15 at 13:35
I have an answer already, but before posting it I have another question: If the Calculation is set to `Manual`, apart from this attempt to detect changes to the `AutoFilter`, how do the users ensure the calculations are updated in any other situation (i.e. changes performed to the data but no changes to the `AutoFilter` are you using `Worksheet_Events` in all other cases? — EEM, Oct 13 '15 at 17:31
The data doesn't change often, but when it changes, I can force a full update by issuing text-to-column on the affected columns via a macro. This forces all the functions and UDFs to recalculate no matter what any calculation setting is set to. I don't use `Worksheet_Events` for anything at all. — Vegard, Oct 13 '15 at 21:14
I see, only missing calculation is when user changes the autofilter. However, this does not trigger any events, as such only option available is to have the user triggering worksheet calculations with an action i.e. selecting another cell, etc; or just pressing [F9]; which is the preferable one as there is nothing else involved. Nevertheless, if you insist I can provide code that is triggered by an event and runs the calculations, but the user has to take an additional action after changing the filter, minor but has to do something. — EEM, Oct 13 '15 at 21:45
If it is less "intrusive" than `Worksheet_SelectionChange` I am interested to see it! — Vegard, Oct 13 '15 at 21:47

Gary's Student · Accepted Answer · 2016-05-23T14:24:35.310

2

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!

edited May 23 '16 at 14:24

answered Oct 12 '15 at 12:40

Gary's Student

95,722
10
59
99

You mean like `=SUBTOTAL(3, tableName)` ? I have tried this as well, but the subtotal does not recalculate. That, combined with the fact that text-to-column does not appear to work on language-localized functions, was my motivation for writing the UDF in the first place. – Vegard Oct 12 '15 at 12:50
If you have calculation set to manual, the formula naturally won't recalculate... – Rory Oct 12 '15 at 13:09
That was my understanding as well, which is why I am attempting to find a workaround. – Vegard Oct 12 '15 at 13:30
This is a great way to detect when tables (ListObjects) are filtered, either on the table or via Slicer... – aSystemOverload May 23 '16 at 11:56
@aSystemOverload I agree, but if you have many formulas in the worksheet, your code must be able to distinguish false hits from true hits...... – Gary's Student May 23 '16 at 11:59
Very true, I initially tried =now(), but as I have multiple sheets with tables I need to detect filters on, it failed. This was the more efficient version we need something even better that will help when you have multiple formula on a worksheet. – aSystemOverload May 23 '16 at 13:13
@aSystemOverload I will update my answer later today. – Gary's Student May 23 '16 at 13:23
@aSystemOverload See my **EDIT#1:** – Gary's Student May 23 '16 at 14:24

EEM · Answer 2 · 2016-12-20T11:10:22.333

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:

Any cell of the Table
The Header of the Table
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)

Detect Autofilter changes

2 Answers2

Linked