Sleeper: Allow autofilter on protected worksheet

[malik641]

What is the correct code in VBA to allow sorting for excel 2000??? I recorded a macro and tried to use the code found in there, but it is not working for me (maybe it is because I have the code in an workbook_open event).

Any ideas? Here is my code, I only want to set the "Bio-Analytical" worksheet to have the sort option:


Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Worksheets("Bio-Analytical")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="", _
contents:=True, userinterfaceonly:=True
End With
With Worksheets("Freezer Diagrams")
.Protect Password:="", _
contents:=True, userinterfaceonly:=True
End With
With Worksheets("ReadMe")
.Protect Password:="", _
contents:=True, userinterfaceonly:=True
End With
ActiveWorkbook.Protect "", Structure:=True
End Sub

[Sir Babydum GBE]

Joseph,

I'm not a coder, but if you do not get another more suitable answer, can you not create a control that will do your sorting? The user clicks the button(s) and the code unprotects, sorts, and re-protects.

[Jacob Hilderbrand]

So you want to allow auto filter on a protected sheet, is that correct? This is an option for Excel 2002 and up, but not for 2000 if memory serves.

[MWE]

What is the correct code in VBA to allow sorting for excel 2000??? I recorded a macro and tried to use the code found in there, but it is not working for me (maybe it is because I have the code in an workbook_open event).

Any ideas? Here is my code, I only want to set the "Bio-Analytical" worksheet to have the sort option:



Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Worksheets("Bio-Analytical")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="", _
contents:=True, userinterfaceonly:=True
End With
With Worksheets("Freezer Diagrams")
.Protect Password:="", _
contents:=True, userinterfaceonly:=True
End With
With Worksheets("ReadMe")
.Protect Password:="", _
contents:=True, userinterfaceonly:=True
End With
ActiveWorkbook.Protect "", Structure:=True
End Sub



I believe that you are trying to do the following:




when the spreasheet opens test to see if the sheet "Bio-Analytical" has autofilter on; if not set autofilter for col A on.
set protection for "Bio-Analytical" with a null password
set protection for the other sheets with a null password.

I placed your unmodified code in the ThisWorkbook module and it seems to work. Protection is set as I think you want and you can autofilter the target sheet even though portection is on. However, there are several ways I could make your code fail:

if any of the hardcoded sheet names did not exist
if there is no data in col A of the sheet "Bio-Analytical"
if there were more worksheets added (and no changes to the code)
if "Bio-Analytical" is protected but autofilter is off (can not set autofilter when protection is on)

Therefore, I would recommend (at least) the following changes:

set up a loop to sequence through all sheets; that will eliminate the need to add code each time you add a sheet
test for the key sheet name, i.e., "Bio-Analytical" and if true

unprotect sheet (no need to check if it is protected)
check for data in col A and if data is present perform the autofilter test


set protection for the current sheet

The code below is one example of how this might be done



Private Sub Workbook_Open()
Dim xlSheet As Worksheet
Application.ScreenUpdating = False
For Each xlSheet In Worksheets
If xlSheet.Name = "Bio-Analytical" Then
If xlSheet.AutoFilterMode = False _
And _
xlSheet.Cells(1, 1).Text <> "" Then
xlSheet.Unprotect
xlSheet.Select
xlSheet.Range("A1").Select
xlSheet.Range("A1").AutoFilter
xlSheet.EnableAutoFilter = True
End If
End If
xlSheet.Protect Password:="", _
contents:=True, userinterfaceonly:=True
Next xlSheet
ActiveWorkbook.Protect "", Structure:=True
End Sub

[Ken Puls]

Hi Joseph,

I just changed your thread title a bit to clarify it. Autofilter is a bit different than sorting. ;)

[malik641]

Hold on....I DO want Sort. I know the difference :yes....let me re-phrase this.

In Excel 2000, there is no option in the autofilter to "Sort Ascending/Decending". You have to go to Data->Sort, which is not accessible if the worksheet is protected. I want to enable this feature. I had already enabled the autofilter feature. But even in Excel 2003 the "Sort" won't work when the sheet is protected (unless you tell it to be when you protect the sheet manually [without code] and the Checkbox Prompt comes up).

And I adjusted my code a bit. I realized that I don't need the If statement in there at all (because the autofilter is always there...unless unprotected and disabled....but the users will not be doing this)....If I did want to check and reset the autofilter feature it would be from Columns A through I and rows 1 to 5000.

But anyway here is my code again (a little modified...not a big deal):


Private Sub Workbook_Open()
With Worksheets("Bio-Analytical")
On Error Resume Next
.EnableAutoFilter = True
.Protect Password:="", _
Contents:=True, UserInterfaceonly:=True
End With
With Worksheets("Freezer Diagrams")
.Protect Password:="", _
Contents:=True, UserInterfaceonly:=True
End With
End Sub

And I want to enable the SORT feature please. Sorry for the misunderstanding.
Thanks

[Ken Puls]

Hi Joseph,

Try a play with this:


Sub test()
With ActiveSheet
On Error Resume Next
.Rows("2:2").AutoFilter
.Protect Password:="", _
Contents:=True, UserInterfaceonly:=True, _
AllowSorting:=True, AllowFiltering:=True
End With
End Sub

[malik641]

The code didn't work correctly. Get rid of the "On Error Resume Next" and you'll notice that you will get an "Application-defined or Object-defined Error" and I'm pretty sure this is because Excel 2000 doesn't have "AllowSorting" and "AllowFiltering" for VBA, which is why I had to use ".EnableAutoFilter" for the autofilter in my original code. Here is where I get the error:


.Protect Password:="", _
Contents:=True, UserInterfaceonly:=True, _
AllowSorting:=True, AllowFiltering:=True

And it makes sense, because in excel 2003 there's a dialog box with a checklist of what you want the user to be able to do while the sheet is protected. Excel 2000 doesn't have this feature, so I'm pretty sure this Error is an "Application-defined Error". I'm just trying to find the correct code to "enable sorting" like I did with the autofilter....:think:

BTW I only have Excel 2000 and 2003, I don't know much about excel 2002. So I don't know if the information I stated about excel 2003 holds the same for 2002. :dunno

[Ken Puls]

Ahh! Sorry Joseph. I have access to Excel 97 and 2003 only.

I found that as long as I set up the autofilter, I could get the autofilter to work whether I added that "AllowFiltering" to the protection or not. I couldn't turn it on if I protected the sheet without the autofilter already in place though. Likewise, I could not get sorting to be available no matter what I did. (2003). I've never tried this before, so must have missed something.

I'm not 100% sure what "EnableAutoFilter" does. (Haven't looked it up yet.) I would be surprised to find that it allows sorting on the sheet though.

[Zack Barresse]

It does just that Ken, but it's not an option in 2000, only in 2002 and later. The syntax isn't even recognized by the Object Model so you'll receive the Application Object.. error.

[Ken Puls]

Sorry, Zack... clarify please...

EnableAutofilter just enables the ability to autofilter on the sheet, yes?

[Zack Barresse]

That is a property of a worksheet. It is a boolean value, either True or False. From the Help files ..


True if AutoFilter arrows are enabled when user-interface-only protection is turned on.
Not very much help Help is.. So setting it to True will enable the Autofilter arrows on a protected sheet.