Search code examples
vbaexcelfilterautofilter

VBA: How do I allow filters to be turned on and off on a protected sheet?

I have the following piece of code, which removes filters and then re-applies them from selected cells:

Range("A10:AM10").Select

Selection.AutoFilter
Selection.AutoFilter

The problem I have is that when the sheet is protected and I try to run the code it errors, so is there a way of allowing filters to be turned on and off on a protected sheet?

Solution

  • How are you?

    It will depend on the setup of the protection. When you protect a worksheet, there is an option to allow filtering (AutoFilter).

    If you're the one who's protecting the sheet, you can use any of the worksheet's protect method's parameters to set the protection options. Here are the protect method's parameters:

    sheetName.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

    If you want to be able to implement filters only by code, then you can set the UserInterfaceOnly parameter to TRUE. The protection will block only the users changes. Code changes will be fully allowed.

    sheetName.Protect UserInterfaceOnly:=True

    Or, if you want to allow users to filter the contents of the worksheet from the interface, just set the AllowFiltering parameter to TRUE as well as the UserInterfaceOnly parameter.

    sheetName.Protect UserInterfaceOnly:=True, AllowFiltering:=True

    Your code should work with both options.

    Hope it helps!