Microsoft Office Tutorials and References
In Depth Information
Using AutoFilter on a Protected Sheet
Using AutoFilter on a Protected Sheet
This macro enables you to use worksheet protection, while still allowing the
end user to use the AutoFilter and/or Outline capabilities.
Example file:
E006.xls
Scenario: Prior to Excel 2002 (XP), it was not possible to
protect a sheet AND still be able to use the AutoFilter. The
same problem applies to using Outline, which is still not
possible in Excel 2003. This procedure provides for these
capabilities.
Exl
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub EnableAutoFilter()¶
'Variable declaration¶
'Worksheet to enable Autofilter¶
Dim WS As Worksheet¶
'Password to protect the sheet (optional)¶
Dim Pwd As String¶
'Change the following two variables accordingly¶
'Set the password to nothing¶
'Pwd = ""¶
'Set the password programatically¶
'Pwd = "YourPassword"¶
'Set the password via user input¶
Pwd = Range("F1").Value¶
'Hard code the worksheet reference¶
'Set WS = ThisWorkbook.Worksheets("Sheet1")¶
'Set the worksheet to the active sheet at the time¶
'the macro is started¶
Set WS = ActiveSheet¶
With WS¶
.Protect Password:=Pwd, Contents:=True, _¶
UserInterfaceOnly:=True¶
.EnableAutoFilter = True¶
'To enable outline uncomment the following line¶
'.EnableOutlining = True¶
End With¶
End Sub¶
 
Search JabSto ::




Custom Search