Keep Data filtering functional when protectiong Worksheet

  • Thread starter Thread starter Phil C
  • Start date Start date
P

Phil C

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
 
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.
 
Thanks Tom.

I will use the macro.
-----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





.
Click to expand...
 
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
 
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.
 
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
Click to expand...
UserInterfaceOnly:=True in the macro.
 
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
 
Back
Top