Run Macro On AutoFilter Use

    Hi all,


    I am fighting a quixotic battle against a problem, that I realise may well be unsolvable. I have a worksheet that consists of approximately 27,000 rows in a four-level structure, like so:


    1
    1.1
    1.1.1
    1.1.1.1


    To make the sheet easier to navigate I have created an expanding/collapsing tree structure by using simple hide and unhide rows, and on the last level of headings (1.1.1) I have cells that change between "+" and "-" depending on whether the fourth-level subset is currently visible or hidden. Naturally I need to keep the cell as "+" when the set is collapsed, and "-" when expanded. I have accounted for all methods user can change the hidden settings of those rows, but one:


    When users autofilter the list, the +/- signs obviously screw up, because filtering resets the user-set hidden-settings. Suddenly I have expanded subsets with "-" on headings that were previously "+".


    Now this is obviously a vanity problem, as the +/- sign is not integral to the working of this macro, but the problem is that with three different levels of headers the worksheets starts to look a little cluttered and these signs really help.


    Finally, my question: Is there any way to trigger a procedure to reset these signs upon/after autofilter? I realize that this is not a built-in Excel event, but a workaround will do just fine. Any way to detect that an autofiltering has taken place without having to check the .Filtermode every second?


    Any help would be greatly appreciated. If you think that an example of the code/workbook might help, let me know, and I'll try to strip a small example out of this monster of a spreadsheet.

    Re: Running A Procedure Upon/after Autofilter


    I've got a feeling that, assuming you have Automatic Calculation enabled, then applying a filter triggers a recalc event (not 100% sure about this though) so you could link it to the Worksheet_Calculate() event.


    Richard

    Re: Running A Procedure Upon/after Autofilter


    Yes, any Volatile Function and the SUBTOTAL Function will fire upon the use of AutoFilter. So you could use a dummy worksheet with ONLY a SUBTOTAL Function on it that refers to the problem Worksheet. Then right click on the Worksheet name tab, choose View Code and use this, change Sheet2 code name to the CodeName of your problem Worksheet;

    Code
    Private Sub Worksheet_Calculate()
    Me.Visible = xlSheetVeryHidden
    Sheet2.UsedRange.ClearOutline
End Sub

    Re: Running A Procedure Upon/after Autofilter


    Quote from Dave Hawley

    Yes, any Volatile Function and the SUBTOTAL Function will fire upon the use of AutoFilter. So you could use a dummy worksheet with ONLY a SUBTOTAL Function on it that refers to the problem Worksheet. Then right click on the Worksheet name tab, choose View Code and use this, change Sheet2 code name to the CodeName of your problem Worksheet.


    Beautiful! I never even considered this kind of workaround. Thanks, Dave.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login