VBA - Excel Filters - Excel AutoFilter on a Protected Worksheet

Post date: Jul 19, 2011 3:43:17 PM

You can use an Excel AutoFilter on a protected worksheet, but you can't create an Excel AutoFilter on a protected worksheet. Ensure that the filter is in place before the sheet is protected.

In Excel 2002 and later versions, you can allow users to use AutoFilter when you set the worksheet protection. (see Protect Sheet dialog box at right)

In previous versions of Excel, use a Workbook_Open macro to set the protection to user interface only. Store the following Excel AutoFilter VBA code on the ThisWorkbook module sheet. It also turns on the AutoFilter is one is not in place:

Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Data") If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With End Sub

To access the ThisWorkbook module, right-click the Excel icon to the left of the File menu, choose View Code, and paste the code where the cursor is flashing