Check if filter applied to column - VBA

Hi,

I was looking for some VBA help if possible! I'm a complete beginner to VBA, but I've researched the web & I've found a piece of VBA that does a lot of what I'm hoping for - but I can't find a way to edit it to be 100% what I need.

Background: My workbook has a data table (not pivot table) which is being filtered by potentially multiple slicers that the user controls. I need to know which columns are being filtered (i.e. which slicers are currently in use) as there could be multiple at once.

The below code is doing a good job of telling me which column numbers are currently filtered, but there's 3 problems;



1. The VBA is launched by running a macro. I would prefer it if the VBA ran automatically any time a slicer is used, I would assume setting the VBA to run on user click might work (I understand from reading it might slow the workbook down, but I'm willing to try it).

2. The VBA only currently works when the users cursor is placed in the data table itself (named: Query1) otherwise it errors out with "Object Variable or With block variable not set" but in order for point 1 above to work, I guess I need to set the "With" to work with Query 1, I've also seen ListObject mentioned - but I'm not sure how.

3. The VBA currently returns me my column number in a msg box, but I'm hoping for it to not show a msg box, but instead set the cell value in row 5 of the relevant column to "Yes" if that filter is currently in use. i.e. if the msg box pop up's to say "12" currently it tells me that there's a filter in place on column L, instead of a msgbox, I would like it to set range ("L5") to "Yes", then loop through the rest of the filters. If the msg box would then pop up again to say "13", I would like it instead to set range ("M5") to "Yes".

I can provide a sample workbook if easier? Can anyone help me with any/all of the above points? Many thanks!!


CURRENT VBA

Sub GetColumns()
Dim Sht As Worksheet
Dim i As Long

Set Sht = ActiveSheet

With Sht.AutoFilter
For i = 1 To .Filters.Count
If .Filters(i).On Then
MsgBox .Range(1, i).Column
End If
Next i
End With
End Sub

Basically:

Please Login or Register  to view this content.

You could call this from a Worksheet_Calculate event assuming you have at least one formula on the sheet that reacts to the filtering.

If your table starts in column A, then you could replace this:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

Glad we could help.