Forum Discussion

slibao's avatar
slibao
Copper Contributor
Nov 03, 2023

Trying to allow autofilter to be cleared with VBA code?

Hi there,

 

I'm currently using a VBA code to filter the sheet whenever certain cell values are changed. On top of that, I've protected certain cells from being modified through the excel protect function. Unfortunately as soon as the code runs, people aren't able to clear the filter (when sheet is protected) so that they can insert new columns. Any idea on how I can do this by modifying my current VBA code below?

 

Thanks so much!

 

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub

Dim rgCriteria As Range
Set rgCriteria = Me.Range("E6:F7")
If Not Intersect(Target, rgCriteria) Is Nothing Then
Me.Range("E10:E8000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rgCriteria
End If

End Sub

 

excel
Macros and VBA

3 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 04, 2023

    slibao 

    To allow the autofilter to be cleared with VBA code even when the sheet is protected, you need to temporarily unprotect the worksheet, clear the autofilter, and then protect the worksheet again. Here is how you can modify your existing VBA code to achieve this:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    Dim rgCriteria As Range
    Set rgCriteria = Me.Range("E6:F7")

    If Not Intersect(Target, rgCriteria) Is Nothing Then
        ' Unprotect the worksheet
        Me.Unprotect

        ' Clear the AutoFilter
        On Error Resume Next
        Me.ShowAllData
        On Error GoTo 0

        ' Apply the new filter
        Me.Range("E10:E8000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rgCriteria

        ' Protect the worksheet with the desired protection settings
        ' Replace "Password" with your desired password, or leave it empty for no password
        Me.Protect Password:="Password", UserInterfaceOnly:=True
    End If
End Sub

     

    Code is untested, before you try it, please backup your file.

     

    In the modified code:

    1. The worksheet is unprotected using Me.Unprotect.
    2. The Me.ShowAllData method is used to clear the autofilter.
    3. After applying the new filter, the worksheet is protected again using Me.Protect. You can specify your desired protection settings and password in the Protect method.

    Make sure to replace "Password" with your actual password or leave it empty if you do not want to set a password for protection. This way, users will be able to clear the filter while the sheet is protected. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

    • slibao's avatar
      slibao
      Copper Contributor
      Nov 04, 2023
      Thank you for that. It seems to still grey out the "clear filter" icon when I protect the sheet, unfortunately.
      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Nov 04, 2023

        slibao 

        The "Clear Filter" icon is greyed out when the sheet is protected.

        You might need to work around this limitation by allowing users to clear the filter using a custom button or macro.

        Here is a workaround using a custom button:

        1. Insert a Button (Form Control) on the worksheet.
          • Go to the "Developer" tab (if not visible, you may need to enable it in Excel's options).
          • Click on the "Insert" drop-down in the "Controls" group.
          • Select "Button (Form Control)" and draw a button on the worksheet.
        2. Right-click the button, choose "Edit Text," and give it a label like "Clear Filter."
        3. Right-click the button, select "Assign Macro," and create a new macro (e.g., "ClearFilter") or choose an existing one.
        4. In the VBA editor, write a macro to clear the filter:

        Vba code:

        Sub ClearFilter()
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
End Sub

        See the example file.

        5. Now, when users click the "Clear Filter" button, it will run the "ClearFilter" macro and clear the filter even when the sheet is protected.

        This approach allows users to clear the filter without needing to unprotect the entire sheet. It provides a more user-friendly way to manage filters, especially when the sheet is protected to prevent changes to other cells.

Resources