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 f...
excel
Macros and VBA
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