Allow Auto Filter Within Macro in Excel 2003 (SP2)
 

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?

Allow Auto Filter Within Macro in Excel 2003 (SP2)
 

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Pank" wrote in message ...
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?

Allow Auto Filter Within Macro in Excel 2003 (SP2)
 

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.


"Ron de Bruin" wrote:

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Pank" wrote in message ...
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?