Setting AutoFilter to "Show All" when worksheet is opened

[Guest]

Can someone tell me how to write a macro that will:

1) automatically run when I open a specific workbook
2) set AutoFilter to "Show All" on the "Summary" worksheet

While this may sound easy at first, please note that the aforementioned
workbook is Shared (Tools | Share Workbook...) and the worksheet ("Summary")
is protected. As a result, the "Show All" option within AutoFilter is
disabled due to the worksheet being protected. And because the workbook is
shared, you can't unprotect the workbook!

Any help would be greatly appreciated.
Thanks,
Bob

 

[Guest]

What version of Excel are you using? If xl2002 or later, did you look at the
enablefiltering option.

 

[Guest]

Tom,

I'm using Excel 2003. I'm not sure I know what you mean by "enablefiltering
option". I'm a VBA novice.

Regards,
Bob

 

[Guest]

When you first protect the sheet (Tools=>Protection=>Protect sheet), in the
dialog you have choices to enable, like Select Unlocked cells. On of the
choices is Autofilter. You may have to scroll down to see it.

Make sure it is checked and see if that works for a shared workbook.

 

[Guest]

Tom,

Yes, I have "Use AutoFilter" checked when I protect the worksheet. But once
the worksheet is protected, the "Show All" option under Data | AutoFilter
becomes disabled. Hence, the reason why I asked if it could be manipulated
via VBA instead.

Regards,
Bob

 

[Guest]

I never use shared workbooks, but to the best of my knowledge, that is what
is available to you. If it doesn't work then you may have a problem.
Try unlocking all the cells in the workbook, then protect it with your
settings and share the workbook. See if it works under those conditions. If
it does, then you can try to see if having certain cells locked might
contribute to the problem.