Auto Filter and Group/Outline on a protectedsheet - using VBA on Excel 2004

[XsilOnMac]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Hi there,
I'm wondering why the following code does not work on Excel 2004.. it works just fine on PC:

Sub AutoFilterTest()

With Sheets("SummaryBudget")
.Protect Password:="justme", userinterfaceonly:=True
.EnableAutoFilter = True
.EnableOutlining = True

End With

End Sub

 

[Laroche J]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Hi there,
I'm wondering why the following code does not work on Excel 2004.. it works
just fine on PC:

Sub AutoFilterTest()

With Sheets("SummaryBudget")
.Protect Password:="justme", userinterfaceonly:=True
.EnableAutoFilter = True
.EnableOutlining = True

End With

End Sub

In the Mac version, AutoFilter just doesn't work on a protected sheet.
See the recent posts titled "sorting a protected sheet" for other
information.

JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[XsilOnMac]

Thanks JL. I saw the "sorting a protected sheet" post, but I was hoping for some more insight.

I understand that you cannot use Filter on a protected sheet in Excel on a Mac. However, the question remains: why doesn't the VBA code fail then?? I would have at least expected some kind of 'property not supported' error message, or something similar. Instead, the macro executes all the way through without error messages, but of course there is no impact on the sheet & it's filters afterwards.

Any ideas?

Thank you

 

[Laroche J]

Thanks JL. I saw the "sorting a protected sheet" post, but I was hoping for
some more insight.

I understand that you cannot use Filter on a protected sheet in Excel on a
Mac. However, the question remains: why doesn't the VBA code fail then?? I
would have at least expected some kind of 'property not supported' error
message, or something similar. Instead, the macro executes all the way through
without error messages, but of course there is no impact on the sheet & it's
filters afterwards.

Any ideas?

Thank you

Your questions led me to do more experimenting, and I almost have to believe
that it's a bug in Excel.

First, Worksheets(1).EnableAutoFilter is not what's used to switch on
AutoFilter. According to VBA help, "EnableAutoFilter Property: True if
AutoFilter arrows are enabled when user-interface-only protection is turned
on." Note: the arrows only, not the function itself.

To actually switch on AutoFilter you need
Worksheets(1).Range("A1").AutoFilter. It's a simple toggle: each time you
execute that line AutoFilter changes state.

But whether EnableAutoFilter is set to true of false, AutoFilter is dead and
arrows appear even if switched on before locking the sheet. That's where
Excel is at fault.

So for your specific point about an error message not showing in your
example: EnableAutoFilter is a simple property designed to set a switch for
protected sheets, so it's normal behaviour to be message-less in any case.
However try to switch on AutoFilter on a protected sheet and you *will* get
an error message.

JL
Mac OS X 10.4.11, Office v.X 10.1.9