Keep Data filtering functional when protectiong Worksheet
It should be in a general module.
If you want to use thisworkbook, you should use the workbook)_open event
Private Sub Workbook_Open()
Worksheets("sheet1").Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub
--
Regards,
Tom Ogilvy
Phil C wrote in message
...
I changed it but still can't get the dropdown arrows to
function while protected. Is there any specific place this
macro should reside. I have it in "This Woekbook"
-----Original Message-----
There was a subtle typo in the macro. Change it to
Public Sub Auto_Open()
Worksheets("sheet1").Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub
Previously the colon was missing in
UserInterfaceOnly:=True in the macro.
--
Regards,
Tom Ogilvy
Phil C wrote in message
...
Tom;
I entered the macro in the Workbook I wanted to protect.
It still locks the auto filter dropdown arrows when the
worksheet is protected. The only thing available is
advanced filter, which may be too complicated for novice
users. What could I be doing wrong? The help menu in VBA
shows the same macro lines as you suggested and
explained
that the filter would be available when the worksheet is
protected. So I know I must be missing something.
Thanks
Phil
-----Original Message-----
You need to set the enableAutofilter property to true
and
set protection to
UserInterfaceOnly:=True. The latter can only be done
with a macro. The
former needs to be done each time a worksheet is
opened. - So I would say
you need a special macro
Public Sub Auto_Open()
worksheets("sheet1").Activate
Activesheet.EnableAutofilter = True
Activesheet.Protect UserInterfaceOnly = True
End Sub
as an example.
--
Regards,
Tom Ogilvy
Phil C wrote in message
...
Good morning.
Is there any way to keep the data filtering function
in
tact while, at the same time, protecting various
selected
cells in a spreadsheet? I am using Windows 2000 and
Office
Excel 2000.
Can this be done without the use of a special macro?
I
tried unchecking various combinations of the
Protection,
I.E. Objects,Contents,Scenarios, but I can only
unlock
all
the cells or the data filtering becomes unusable
also.
Thanks
Phil
.
.
|