hi, Mike !
Is there a way to detect a change in a sheet's Autofilter Selection?
ie... when I change Autofilter for column 5, I need to get what the new values are for Title, Criteria1 etc...
following is an "ancient" procedure I used in the filtering worksheet code module (_calculate event)
to put a formula in cell [E2] and linked to a textbox (shape drawing)
hth,
hector.
Private Sub Worksheet_Calculate()
Dim myFormula As String, nFilter As Integer, Filtered As Integer, Filtering As String
If Not Me.AutoFilterMode Then Exit Sub
Application.ScreenUpdating = False
myFormula = "=""Filtering by:"""
With Range(Me.AutoFilter.Range.Address)
For nFilter = 1 To .Columns.Count
With .Cells(1, nFilter)
If Me.AutoFilter.Filters(nFilter).On Then
Filtered = Filtered + 1
Filtering = CStr(.Value)
With Me.AutoFilter.Filters(nFilter)
myFormula = myFormula & "&" & _
"Char(10)" & "&""" & Filtered & ".- " & Filtering & ". Criteria " & .Criteria1
If .Operator = xlAnd Then myFormula = myFormula & " AND 2nd criteria " & .Criteria2
If .Operator = xlOr Then myFormula = myFormula & " OR 2nd criteria " & .Criteria2
myFormula = myFormula & """"
End With
End If
End With
Next
End With
If Filtered = 0 Then myFormula = _
"=""Actually""" & "&" & "Char(10)" & "&" & """There is NO active filters !!!"""
Me.Range("e2").Formula = myFormula
End Sub
