Allow Auto Filter Within Macro in Excel 2003 (SP2)

[Pank]

I have the following macro which asks the user for a password and then
passwords all sheets within a workbook which works exactly as I want.

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If WS.ProtectContents = True Then
Title = "Sheet already Protected"
Style = vbCritical
Message = WS.Name & " already protected"
MsgBox Message, Style, Title
E = E + 1
Else
Title = "Doing Protection"
Style = vbInformation
Message = WS.Name & " protected"
MsgBox Message, Style, Title
WS.Protect (PasswordEntered)

End If

However, I have now discovered that I need to allow Auto Filter and have
inserted the following line just before the ENDIF:-

WS.EnableAutoFilter = True

Unfortunately once the macro has run, the Auto Filter option is not showing
the showing.

I have tried to manually protect the sheets using Tools, Protecting, Protect
Sheet and selected Use AutoFilter and I get the desired effect.

What do I need to do to enable the macro to work properly?

 

[Ron de Bruin]

You can use it in the protect line

WS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True

Or see
http://www.contextures.com/xlautofilter03.html#Protect

 

[Pank]

Ron,

Firstly, Thank U for your valuable input, I will try it out.

Secondly, sorry for the delay in 'saying Thank you', I have just got back to
work from a week on the sick.