[RESOLVED] Need help trapping data filter on/off

[doasidont]

I need a way, using VBA, to determine when data filtering has been turned on or off and after that whether the number of hidden rows (due to filtering) has changed.

Using Excel 2007. Any help would be appreciated.

[baja_yu]

You missed the forum section. This is for VB Classic. I'll notify Mods to move it to the correct section.

[MartinLiss]

Moved.

[Siddharth Rout]

Sub AutoFilterStatus()
    Dim xlWorksheet As Worksheet

    On Error GoTo Err
    
    '~~> Set the active sheet
    xlWorksheet = ActiveSheet

    If Not xlWorksheet.AutoFilter Is Nothing Then
        If xlWorksheet.FilterMode = True Then
            MsgBox ("Auto Filter On: Filter Mode On")
        Else
            MsgBox ("Auto Filter On: Filter Mode Off")
        End If
    Else
        MsgBox ("Auto Filter Off")
    End If

    If Not xlWorksheet Is Nothing Then xlWorksheet = Nothing

Err:
    If Err <> 0 Then
        MsgBox Err.Number & " - " & Err.Description
        Resume Next
    End If
End Sub

[doasidont]

Thanks for quick response. I think your code will tell me if filtering is on or off, but I need something a little more complicated. My problem is that I need to know if the number of hidden rows has changed due to a data filtering operation. If, for instance, the number of hidden rows has changed because row grouping was changed, then I don't need to perform some action. If, however, someone has performed a data filtering command and the number of hidden rows has thereby changed, then I need to perform some action. The action that's needed when someone has data filtered, by the way, is to resequence a row identifier column so that alternate row shading (through conditional formatting) can be maintained.

Thanks.

[SQLADOman]

In Excel 2003 and I believe 2007/10, using the SUBTOTAL() function instead of the ROW() function in the conditional formatting formula causes alternating rows to be shaded regardless of hidden rows caused by auto filtering.http://blogs.techrepublic.com.com/10things/?p=1576

[doasidont]

Thanks, but that won't work either. Reason is that it's not just alternating rows that need to be shaded. This is an inventory workbook. Each item in inventory has bin locations where the items are located. The shading requirement is that the item with all of its bin locations are shaded identically and every other item (with its bin locations) is shaded. So if item 1 on row 2 has three bin locations, item 2 on row 6 has two bin locations, and item 3 on row 9 has four bin locations, the shading would be: rows 2-5 are unshaded, 6-8 are shaded, and 9-12 are unshaded, etc.

[SQLADOman]

I do apologize, as I am not feeling sharp enough at the moment to fully wrap my head around what your requirement is.

Does this routine offer any assistance?

Sub Filtered_Yes_No()
'Derived from Procedure "CountVisRows" by Tom Ogilvy
'http://www.contextures.com/xlautofilter03.html

Dim rng As Range

Set rng = ActiveSheet.AutoFilter.Range

If Not rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 _
                                   = rng.Columns(1).Rows.Count - 1 Then

        'MsgBox ("Rows are Filtered")
        'Code here to run if rows are filtered

    Else

        'MsgBox ("Rows are not Filtered")
        'Do nothing

End If

End Sub

[doasidont]

I'm getting an 'Object Variable or With Block variable not set' message on the 'Set' line. What does the sheet.autofilter.range instructon do?

Anyway, I don't think this code will work either, because I think the code should be placed in the worksheet/calculate event module, right? If so, it would be triggered if the user does a row group or ungroup, or even a row hide or unhide. I need the code to be triggered only when a data filter operation changes the hidden row count. Logic something like this: Was data filtering turned on or off and if so, did the number of hidden rows change? If yes, do the code; otherwise do nothing.

Maybe I'm over complicating this, but all I want to do is preserve the alternate shading formats for items and their bin location rows even when the user filters out, or in, some of the items.

Thanks.

[SQLADOman]

The command Set rng = ActiveSheet.AutoFilter.Range
merely sets the range that is included in the Auto Filter to the rng variable.

The Macro below will demonstrate:

Sub Range_Included_in_Auto filter()

Dim rng As Range

With ActiveSheet
  If .AutoFilterMode = True Then
     Set rng = .AutoFilter.Range
     MsgBox rng.Address
  End If
End With

End Sub

Is there any chance you could throw together a sample workbook with some sample groupings and the conditional formatting?

The 'Object Variable or With Block variable not set' error that you are getting can likely be avoided with the improved code below:

Private Sub Worksheet_Calculate()

If Not ActiveSheet.FilterMode = True Then

'Exit because the Auto Filter has been released or is turned off
Exit Sub

End If

Dim rng As Range

Set rng = ActiveSheet.AutoFilter.Range

If Not rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 _
                                   = rng.Columns(1).Rows.Count - 1 Then

        'MsgBox ("Rows are Filtered")
        'Code here to run if rows are filtered

End If

End Sub

Edit:
With my testing of the Macro Shown Above
hiding rows manually did not trigger the ("Rows are Filtered") msgbox during Calculation Event's, but I must admit I do not understand why.

Below is alternative code that is easier to understand:

Private Sub Worksheet_Calculate()
'Code derived from here http://www.ozgrid.com/VBA/autofilter-vba.htm
With ActiveSheet

        If .AutoFilterMode = True And .FilterMode = True Then

            MsgBox "Auto Filter is turned on and in use"

        ElseIf .AutoFilterMode = True Then

            MsgBox "Auto Filter is turned on but is not in use"

        Else

            MsgBox "Auto Filter is turned off"

        End If

End With

End Sub

[doasidont]

Found a way to do it. I created a function:

Function Hidden_Row(item)
    Hidden_Row = Cells(item.Row, 1).EntireRow.Hidden
End Function

Then I included that function in a formula in a column (e.g. Column 26) that:
1) starts with '1' if the formula is on row '2' (skip header),
2) uses the same number as in the previous row if the current row is hidden or if the current row is a bin location row, or otherwise
3) adds '1' to the number in the previous row.

=IF(ROW()=2,1,IF(OR(Hidden_Row(R item),R item="Bin"),R[-1]C,R[-1]C+1))

Then the conditional formula uses the mod function against this column of numbers.

=MOD(RC26,2)=1

If an 'item' set (item row together with its bin rows) is hidden (in fact, if a row is hidden by grouping, format, whatever), the alternate shading by 'item' set is maintained.

Thanks for all of your help and suggestions.