Display filter criteria using macro buttons | VBA forum

Hi there

I have written simple macros to autofilter a table of data.

The code for one of them looks like this:

Sub Filter_Region()

Dim Crit1 As Range, bRange As Range

Set Crit1 = Range("I5")

Set bRange = Range("A10:O64")

With bRange
.AutoFilter field:=8, Criteria1:=Crit1.Value

End With

End Sub


These work fine, but I want to display the criteria by which I am filtering, so I have used a function written by someone called Stephen Bullen:

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter

Worksheets("Option 2").Calculate
End Function


I just have to write =showfilter(CELL REFERENCE) - which work fine until....

When I run my 'macro buttons' these display cells do not seem to update, instead I get a #VALUE! error.

I have tried putting =showfilter(F10)&LEFT(SUBTOTAL(9,D11:O64),0) to automatically update them, and also added Worksheets("NAME OF SHEET).Calculate to try to update these automatically but it doesn't work.

Help on detecting what the problem & how to fix these display cells would be fantastic!

Thank you

Also I should add that when I click on the cell with the =showfilter formula, it updates, but I have to go in a click on it and press Enter, to remove the #VALUE! error.