View Full Version : Enable Auto Filter

Krishna Kumar
03-10-2005, 04:38 AM
Hi All,

I'm using the following code to protect the cells after saving the file. This works fine.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim CellsToprotect As Range
Dim Sht As Worksheet
On Error Resume Next
Pass = "hello"
For Each Sht In ThisWorkbook.Worksheets
Sht.Unprotect Password:=Pass
Sht.Cells.Locked = False
Sht.Cells.SpecialCells(xlCellTypeConstants).Locked = True
Sht.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
Sht.Cells.SpecialCells(xlCellTypeComments).Locked = True
CellsToprotect.Locked = True
ActiveSheet.Protect Password:=Pass
Next Sht
End Sub

I have another code which (I got it from MrExcel) supposed to enable the Auto Filter. But unfortunately it doesn't. I'm using XL 2000 and OS XP Proffessional.

Private Sub Workbook_Open()
With ActiveWorkbook.Sheets("Attendance")
.EnableAutoFilter = True
.Protect Contents:=True, userInterfaceOnly:=True
End With
End Sub

Any help would be greatly appreciated.


Jacob Hilderbrand
03-10-2005, 07:44 AM
Try this.

K. Georgiadis
03-10-2005, 03:19 PM
Jake, out of curiosity, would adding the following statement work also?:


Jacob Hilderbrand
03-10-2005, 03:34 PM
You're right, I think I read the question wrong. I thought he was trying to create the auto filter with the macro.

.Protect Contents:=True, userInterfaceOnly:=True, AllowFiltering:=True

Krishna Kumar
03-10-2005, 08:39 PM

That doesn't help me. Still not working.
Any ideas?


Jacob Hilderbrand
03-10-2005, 10:11 PM
Are you using Excel 2002 or 2003?

Krishna Kumar
03-10-2005, 10:19 PM

I already mentioned this in my original post. Anyway I'm using Xl 2000.


Jacob Hilderbrand
03-10-2005, 10:40 PM
Ok, I see that now. Unfortunately this feature is not available for Excel versions before 2002. :(

K. Georgiadis
03-11-2005, 08:03 AM

I got your message that the code worked and another that did not. Assuming that it did not, here is what I have been using successfully (I inserted "hello" as the password as that apparently is what you have been using)* :

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pt As PivotTable
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="hello"
For Each pt In ws.PivotTables
ws.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, _
End Sub

This code resides in "This Workbook" and is designed to unprotect all sheets when the workbook is opened so that the Pivot Table can be refreshed. Then the worksheets are protected again. Note that, in addition to filtering, I have allowed formatting cells and rows, which may be something you don't want.
Also note that the lines of code after the last "Next" statement apply to a Userform (which I also have) and make the book open on cell A1 of a sheet called "Instructions." These are instructions that you will want to delete!

I hope that this helps

* this is an adaptation of code from Debra Dalgleish's website (www.Contextures.com (http://www.contextures.com/)). I must give credit where credit is due!

alright Kris, in the meantime I saw DRJ's post saying that these features are not available in Excel 2000. Sorry about that

Krishna Kumar
03-13-2005, 10:36 PM
Hi Georgiadis,

Thanks for the code. I didn't try your code because I don't want the sheet to be unprotected when the file is open. Actually I want the cells to be protected always once the file is saved.

Once again thanks.


K. Georgiadis
03-14-2005, 06:39 AM
Kris, actually the code unprotects the workbook momentarily so that the Pivot Table can be refreshed, and than it protects it again. The user will not have a chance to do anything before the protection takes effect!

The bigger issue seemed to be whether your version of Excel allows/recognizes the exceptions to the worksheets protection. If so, you can also use the following alternative:

Place this code in a Module and run the protection manually:

Public Sub ProtectAll()

Const PWORD As String = "hello"

Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, DrawingObjects:=False, _
Contents:=True, Scenarios:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True

Next wsSheet

End Sub

As you can see, the code allows filtering in spite of the worksheet protection.

My workbooks typically contain a lot of worksheets therefore, before I make the workbook available to the "public," and while I am still editing it, I also insert a version of the module where "unprotect" replaces the word "protect" in its two occurrences. That way, I do not have to unprotect sheets manually. Just remember to protect the workbook again!

Good luck

Krishna Kumar
03-14-2005, 08:37 PM
Hi Georgiadis,

O.K. I tried your code, but getting a compile error Named argument not found with highlighting this part;

I think the problem is as Jacob pointed out that XL 2000 doesn't allow this feature.

Thanks Georgiadis for your contribution on this topic.


K. Georgiadis
03-14-2005, 08:41 PM
Kris, I'm afraid you're right. I gave a workbook incorporating this code to a fellow who was running Excel 2000 and he got the same compile error.