enable autofilter on protected sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chicco - 18 Dec 2005 23:05 GMT

I have an excel worksheet that I have made in to a managed list, with
autofilters. I need for the formulas to be protected, but once I
protect the sheet, the autofilter function doesn't work. Is it possible
to enable autofilter on a protected sheet on a Mac (the windows version
seems to have a simple way to do this) ? I have tried going to the VBA
editor and checked "enable autofilter," but that didn't work -
Apparently I need to insert a code, unfortunatly I have no knowledge of
VBA. Help would be greatly appreciated.
Thank you.

Reply to this Message

JE McGimpsey - 19 Dec 2005 11:36 GMT

> I have an excel worksheet that I have made in to a managed list, with
> autofilters. I need for the formulas to be protected, but once I
[quoted text clipped - 4 lines]
> Apparently I need to insert a code, unfortunatly I have no knowledge of
> VBA. Help would be greatly appreciated.

Ctrl-click the workbook header bar and choose "View Code" to enter the
Visual Basic Editor (VBE). Delete any code in the module that opens
(titled "ThisWorkbook"), and paste this in:

Private Sub Workbook_Open()
Const sPWORD As String = "drowssap"
With Worksheets(1)
.EnableAutoFilter = True
.Protect Password:=sPWORD, _
Contents:=True, _
UserInterfaceOnly:=True
End With
End Sub

Change the password ("drowssap") to whatever you want (or to "" if you
don't want a password). Change the worksheet number (or enter the name
of the sheet in quotes) if desired. Click the XL icon on the VBE toolbar
to return to XL, then save your workbook. Close and reopen - autofilter
will now work on that sheet.

Reply to this Message

chicco - 20 Dec 2005 03:49 GMT

> > I have an excel worksheet that I have made in to a managed list, with
> > autofilters. I need for the formulas to be protected, but once I
[quoted text clipped - 24 lines]
> to return to XL, then save your workbook. Close and reopen - autofilter
> will now work on that sheet.

Thank you very much for your prompt help,
unfortunately I get the following message: (compile error: expected:
end of statement)
The only change I made is that I took the password out and put ""
instead.
Any suggestions on what I might add or change?
Thanks again

Reply to this Message

JE McGimpsey - 20 Dec 2005 04:39 GMT

> Thank you very much for your prompt help,
> unfortunately I get the following message: (compile error: expected:
[quoted text clipped - 3 lines]
> Any suggestions on what I might add or change?
> Thanks again

When you get the error and click Debug, what line is highlighted?

If you copied and pasted, it should work fine, including with "" for a
password - it works in a test workbook for me.

Check that (a) you copied it exactly, and (b) if you're using a browser,
sometimes non-breaking spaces are inserted in lines. Try deleting all
the white-space before each line and replacing it with tabs in the VBE.

Reply to this Message

chicco - 21 Dec 2005 01:28 GMT

It works!!!!
It was the spacing
Thank You sooooo much.

Reply to this Message

Mac Forum / Applications / Excel / December 2005

enable autofilter on protected sheet