Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detect Autofilter Change Event | Excel Programming | |||
AutoFilter Selection to Change Page Header | Excel Programming | |||
How to detect last row of Selection? | Excel Programming | |||
Autofilter.Selection - After Change Event??? | Excel Programming | |||
How can I detect an AutoFilter when the Criteria isnt Met | Excel Programming |