excel vba‎ > ‎

VBA - Excel Filters - Excel AutoFilter on a Protected Worksheet

posted Jul 19, 2011, 8:43 AM by Roberto Felicini

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
Comments