Autofilter of Protected Sheet - Excel 2000 (not 2003 or 2007)

[DSMessenger]

I have read multiple posts on here about using "Autofiltering=True" when the
Protect function is used to allow Autofiltering.

I can see this (and multiple other sub options) in the Help File for Excel
2003. However I am writing an application for Excel 2000 and this option does
not appear available. I would like the Autofilter for various columns to be
selectable, either by the user or macros, whilst the Worksheet is protected.

Is there anyway around this apart from making users upgrade their version of
Excel ? I am assuming the answer is 'No' but if someone tells me otherwise I
will be very happy.

Thanks in advance.

 

[Peter T]

You need to set UserInterfaceOnly, but as the setting doesn't persist
between sessions need to set it in the open event.

Apply the filter on an unprotected sheet then run the macro. Also run the
macro from the open event.

'' ThisWorkbook module
Private Sub Workbook_Open()
ProtectAndFilter
End Sub

' normal module
Sub ProtectAndFilter()

With ThisWorkbook.Worksheets("Sheet1")
.Protect _
Password:="abc", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True

.EnableAutoFilter = True
End With

End Sub

Regards,
Peter T

 

[DSMessenger]

Thanks Peter. Easily done as I already set-up Commandbars and other things in
the Open Event.

I had also read on here about the UserInterfaceOnly option but had not found
its companion EnableAutoFilter which works in combination with it.

This Discussion Page is 'gold' and 95% on the time I can solve a problem
simply by searching the past posts ... and where that fails the remaining 5%
of the time my post is answered within 24 hours anyway.