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

On Wed, 24 Dec 2008 19:09:17 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 24 Dec 2008 14:22:08 -0800, Neon520
wrote:

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 he
http://office.microsoft.com/en-us/ex...982701033.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