Worksheet Protection Disabling Auto Filter

[suiliclic]

Dear All,

I got really stuck on this problem so any advise would help. Thank you! I am working on an multi worksheets excel file. I am trying to protect a single worksheet name "All Codes_Listing_HH," the protection part works ok but it caused problems to run my macro codes to filter certain things. After I activate below codes under Workbook_Open sub, I can not use the excel option-->Data --> Filter --> Auto Filter. Even the "Show All" Button on my tool bar seems to be disable too.

MY QUESTION IS how do I keep this worksheet protected and able to run macro "A1PrintPreviewFlagY" without any problems. I would like to permit users to use the "Show All" button as well. Everytime I run the macro it seems to stop at the "Rows("1:1").AutoFilter" line of macro codes.
I even tried protecting the worksheet using excel's option and checked -->Allow User to use..AutoFilter under -->Tool --> Protection --> Protect Sheets, and my macro didn't work either.


Here is my codes under the Workbook_Open Sub:
-----------------------------------------------------------
Private Sub Workbook_Open()

With Worksheets("All Codes_Listing_HH")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
End With

With Worksheets("All Codes_Listing_HH")
.Protect Password:="0000", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
.EnableAutoFilter = True

End With
End Sub
----------------------------------------------------------
Here is my codes under the macro codes which were stored under a module:

Sub A1PrintPreviewFlagY()
' This macro will show all records filtered in colum DSH Flag as "Y"
' Also Hide unnecessary columns then stop at print preview

Sheets("All Codes_Listing_HH").Select
Rows("1:1").AutoFilter
Columns("A:BM").EntireColumn.Hidden = False
HideColumns "G:I", "M:N", "P", "R", "U:V", _
"X", "AC:AL", "AN:AS", "AV:BC", "BE:BG"
Rows("1:1").AutoFilter
Rows("1:1").AutoFilter Field:=2, Criteria1:="Y"
ActiveSheet.PageSetup.PrintArea = "$A:$BI"
ActiveWindow.SelectedSheets.PrintPreview
End Sub
------------------------------------------------------------

Thank you very much.


Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~

 

[suiliclic]

*Sign* How come no one answers this Question?! Please help!

Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~

 

[LTillner]

Well This isn't an elegant solution, but it works for me.

In my macro, I unprotect the sheet, run my macro, then protect the sheet again.

you can get that code by turning you macro recorder on,

turn protection off
run macro
turn protection on

stop recorder and clean up and integrade code.

HTH!

 

[eti3nne]

suiliclic,

I haven't tried to use this particular one myself, but if you search on this link there are a number of 'AutoFilter problem' solutions covered (amongst other excellent tips).

http://j-walk.com/ss/excel/eee/eee010.txt