How can I detect change to Autofilter selection
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.... Thanks! |
How can I detect change to Autofilter selection
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 |
All times are GMT +1. The time now is 05:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com