Auto-Filter When Workbook Protected

[Neon520]

Hi everyone,

I'm copying data from several workbooks in a Folder to a separate summary
sheet. I really like the Auto Filter > (Custom Filter...) in Excel. It would
be REALLY REALLY great to have a macro that can do just about the same thing
as what (Custom Filter...) does. By the same, I mean the CRITERIA section of
"equal", "not equal", "greater than" and so on... (You get the point)

However, since I'm suck with Macro or VBA, I'm trying to have creative way
to get my work done. So I'm thinking about why not copying everything to the
summary sheet and then do a custom filtering of auto-filter. But again, I
hit another bump. They are disable by default if the sheet is protected.
The reason I have my sheet protected is I don't others to mess up the formula
in there.

So does anyone knows if there is a way to either IMPLEMENT custom filtering
of Auto Filter in Macro or Enable Custom Filtering when sheet is protected?

Thanks for any help!
Neon520

 

[Gord Dibben]

Sub auto_filter()
With Sheets("Sheet1")
.Range("A1").AutoFilter
.Protect Password:="justme", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

There are other ways to run the code.

Sheet_Activate event comes to mind.


Gord Dibben MS Excel MVP

 

[Neon520]

Thanks for your quick respond, Gord.

I tried the code that you gave me, but Nothing really happen. The screen
just flash, and nothing really get sorted, or if I can choose any filtering
criteria.

Is there a way that I can still have the custom filtering?

Here is the code that I tried:

Sub auto_filter()
With Sheets("Final")
..Unprotect Password:="justme"
..Range("A6:A38").AutoFilter
..Protect Password:="justme", userinterfaceonly:=True
..EnableAutoFilter = True
End With
End Sub

Neon520

 

[Gord Dibben]

Where did "sorted" come into this?

You haven't enabled "sorting" in the code.

The code you posted to set an AF arrow at A6 works for me and I can choose a
custom filter from there.

BUT............you cannot filter on just the range A6:A38 if data exists in
column A below A38 unless row 39 is blank.


Gord

 

[Gord Dibben]

There may be other ways to do what you want.

One example.................If running 2003 you can turn A6:A38 into a List
from Data>List>Create List

In 2007 I believe it would be a Table

You can then filter within that list alone on a protected sheet.

Copy this code below into the sheet module. Right-click on sheet tab and
"View Code" to access that module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objlist As ListObject
Set objlist = Me.ListObjects(1) 'adjust the (1) if needed
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, objlist.Range) Is Nothing Then
Me.Unprotect Password:="justme"
Else
With Me
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

When you click inside the List range you can filter, insert or delete rows
and columns within the List.

When you click outside the List range, locked cells are protected from
change.


Gord

 

[Neon520]

Hi Gord,

I'm not sure if it makes any difference or not in this case that I'm using
Office 2004 for Mac. I know that it supports Macro and VBA, except some
codes are different from the Office for Window.

In Office 04, there's actually a List Wizard that help user to generate
list. And you're right. My list is A6:K38 with A6:A36 as Header Row and
A39:K39 as Total Row. When I put your code as you instructed, here is an
error message that I receive:

Run-time error ‘9’:
Subscript out of range

I tried play around with it by changing the >> Set objlist =
Me.ListObjects(1) << to different number, but nothing really works.

FYI, I did tried a solution found in Office Online Website posted by MS here:
http://office.microsoft.com/en-us/excel/HA010982701033.aspx

Private Sub Workbook_Open()
Sheet1.Protect password:="test", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
Sheet1.EnableAutoFilter = True
End Sub

But it doesn't work at all. Is this because I'm using Office for Mac?

Please explain?

Thanks,
Neon520

 

[Gord Dibben]

I know nothing about Excel on a Mac but a few points to mention.

Did you put the selectionchange event code into the sheet module?

The ListObjects(x) should be (1) if that's the only List you have in the
workbook.

A6:A36 is a range in a single column so cannot possibly be a header row.

Your List header row should be A6:K6

The "solution" you found at the Website requires you to change Sheet1 to
your sheet name and the code goes into Thisworkbook module, not a sheet or
general module.

Private Sub Workbook_Open()
With Sheets("your sheetname")
.Protect Password:="test", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

But take note: the Autofilter arrows must be applied before the sheet is
protected. You cannot apply after.

If you looked at the macro I originally posted that's what the

line .Range("A1").AutoFilter does for you

Sub auto_filter()
With Sheets("Sheet1")
.Range("A1").AutoFilter
.Protect Password:="justme", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub


Gord

 

[Gord Dibben]

To overcome the ListObject(x) you could try this sheet event code assuming
the List range will be fixed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A6:K38"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Unprotect Password:="justme"
Else
Me.Protect Password:="justme"
End If

ws_exit:
Application.EnableEvents = True
End Sub


Gord