WorksheetBase.AutoFilterMode Property

Definition

Namespace:

Microsoft.Office.Tools.Excel

Assembly:

Microsoft.Office.Tools.Excel.v4.0.Utilities.dll

Gets or sets a value that indicates whether filtering is currently enabled on the worksheet (that is, whether the filter drop-down arrows are currently displayed).

public:
 property bool AutoFilterMode { bool get(); void set(bool value); };

public bool AutoFilterMode { get; set; }

member this.AutoFilterMode : bool with get, set

Public Property AutoFilterMode As Boolean

Property Value

Boolean

true if filtering is currently enabled on the worksheet; otherwise, false.

Exceptions

COMException

This property is set to true.

Examples

The following code example creates a filtered list of fruits in a Range, and then uses the AutoFilter property to display the filter criteria for the list. The example then prompts the user to turn off the filter by using the AutoFilterMode property, and finally uses the FilterMode property to display whether the worksheet has a filtered list.

This example is for a document-level customization.

private void ActivateAutoFilter()
{
    this.Range["A1"].Value2 = "Fruits";
    this.Range["A2"].Value2 = "Banana";
    this.Range["A3"].Value2 = "Apple";
    this.Range["A4"].Value2 = "Banana";
    this.Range["A5"].Value2 = "Orange";
    this.Range["A6"].Value2 = "Apple";
    this.Range["A7"].Value2 = "Orange";

    Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
        this.Controls.AddNamedRange(this.Range["A1", "A7"],
        "NamedRange1");

    NamedRange1.AutoFilter(1, "Apple",
       Excel.XlAutoFilterOperator.xlAnd, true);

    MessageBox.Show("The current criteria for the AutoFilter is: " +
        this.AutoFilter.Filters[1].Criteria1.ToString());

    if (this.AutoFilterMode)
    {
        if (DialogResult.Yes == MessageBox.Show("Would you like to " +
            "turn off the filter?", "Example", MessageBoxButtons.YesNo))
        {
            this.AutoFilterMode = false;
        }
    }

    if (this.FilterMode)
    {
        MessageBox.Show("The worksheet has a filtered list.");
    }
    else
    {
        MessageBox.Show("The worksheet does not have a filtered list");
    }
}

Private Sub ActivateAutoFilter()
    Me.Range("A1").Value2 = "Fruits"
    Me.Range("A2").Value2 = "Banana"
    Me.Range("A3").Value2 = "Apple"
    Me.Range("A4").Value2 = "Banana"
    Me.Range("A5").Value2 = "Orange"
    Me.Range("A6").Value2 = "Apple"
    Me.Range("A7").Value2 = "Orange"

    Dim NamedRange1 As Microsoft.Office.Tools.Excel.NamedRange = _
        Me.Controls.AddNamedRange(Me.Range("A1", "A7"), "NamedRange1")

    NamedRange1.AutoFilter(1, "Apple", Excel.XlAutoFilterOperator.xlAnd, _
        VisibleDropDown:=True)

    MsgBox("The current criteria for the AutoFilter is: " & _
        Me.AutoFilter.Filters(1).Criteria1.ToString())

    If Me.AutoFilterMode Then
        If DialogResult.Yes = MessageBox.Show("Would you like to " & _
            "turn off the filter?", "Example", MessageBoxButtons.YesNo) Then
            Me.AutoFilterMode = False
        End If
    End If

    If Me.FilterMode Then
        MsgBox("The worksheet has a filtered list.")
    Else
        MsgBox("The worksheet does not have a filtered list")
    End If
End Sub

Remarks

You can set this property to false to disable filtering (that is, to remove the filter drop-down arrows), but you cannot set it to true. To enable filtering and create a filtered list, use the AutoFilter method of a Range object, or use the AutoFilter method of a NamedRange object.

This property indicates only whether filtering is enabled; it does not indicate whether any data is being filtered. To determine whether the worksheet is actively filtering data, use the FilterMode property.