Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto filter within a macro | Excel Discussion (Misc queries) | |||
Auto filter run from a macro will not do the same as when done manually. | Excel Discussion (Misc queries) | |||
Auto filter run from a macro will not do the same as when done manually. | Excel Discussion (Misc queries) | |||
Auto Filter Macro | Excel Discussion (Misc queries) | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |