enable autofilter on protected sheet

[chicco]

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.

 

[JE McGimpsey]

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.

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.

 

[chicco]

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.

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

 

[JE McGimpsey]

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

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.

 

[chicco]

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