Sleeper: Enable Auto Filter

[Krishna Kumar]

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.

Kris

[Jacob Hilderbrand]

Try this.

Sheets("Attendance").Range("A1:C1").AutoFilter

[K. Georgiadis]

Jake, out of curiosity, would adding the following statement work also?:

AllowFiltering:=True

[Jacob Hilderbrand]

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]

Hi DRJ,

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

Kris

[Jacob Hilderbrand]

Are you using Excel 2002 or 2003?

[Krishna Kumar]

Hi DRJ,

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

Kris

[Jacob Hilderbrand]

Ok, I see that now. Unfortunately this feature is not available for Excel versions before 2002.

[K. Georgiadis]

Kris,

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
pt.RefreshTable
Next
ws.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, _
Password:="hello"
Next
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
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). 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]

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.

Kris

[K. Georgiadis]

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]

Hi Georgiadis,

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

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.

Kris

[K. Georgiadis]

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.