AutoFilter in protected Excel Sheet 5

[DanWo]

If I protect Excel Sheet, AutoFilter don't work.
What can I do to enable AutoFilter in protected sheet?

 

[SkipVought]

Dan,

Via VBA you must
unprotect
filter
... operate on other protected features
protect

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Chattin]

You could use the unprotect/reprotect method - something like

Sub Macro1()

Sheets("Sheet1&quot.Select
ActiveSheet.Unprotect Password:="password"


'your filter code here

ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

HTH

 

[Chattin]

Darn - beaten to it by SkipVought :lol:

 

[xlbo]

As another suggestion - you can use


Private Sub Workbook_open()

sheets("Sheetname&quot.protect userinterfaceonly:=true

end sub


This will allow you to run code on the sheet but to the user it will appear protected. I personally prefer this method as it means you don't have to keep using

sheets("sheetname&quot.unprotect
'code
'code
sheets("Sheetname&quot.protect

every time


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenMcd4]

I'm also seeking a way to allow using autofilters on a protected worksheet. It doesn't seem logical that a user can't use the autofilters to filter a protected sheet, since filtering doesn't alter the contents of the sheet (only the display).

I don't think that unprotecting the sheet in VBA (to enable autofilters), then protecting it again is practical, since the user would have full capability to alter the sheet, as well as setting autofilters.

Is there a way to unprotect (unlock) autofilters, the same way you can unprotect selected cells and objects, while keeping the rest of the sheet protected?

 

[IvanMoala]

something like

ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True

Obviously if you have a password then use thisas well

 

[KenMcd4]

Ivan...

Thanks for the tip - it worked quite well! Where I work (a large corp), we're still using Excel 97. While the features you listed do work in Excel 97, they aren't documented! I have Office 2000 at home, so I was able to print out the online help text from there.

Hopefully DanWo, who originated this thread, will see your post, and also get the solution to his problem (the same as mine).

 

[xlbo]

Bear in mind that the userinterface:=true must be set for each session of the workbook - that's why I put it in the workbook_open event (earlier post)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MeGustaXL]

Nice, Ivan!
I'd given up on ever finding a way to do this.... Have a STAR!


Chris

Varium et mutabile semper Excel

 

[IvanMoala]

xlbo
Bear in mind that the userinterface:=true must be set for each session of the workbook - that's why I put it in the workbook_open event (earlier post)

Thanks xlbo, i forgot to mention this.

 

[mimitan]

I just posted a similar question and VBAJedi referred me to this thread. Having read through and tried on my sheet, I still can not get it to work correctly.
I tried with Ivan's code

ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True

but did not really where to place them.

this is my code:

Private Sub CommandButton1_Click()
Range("AF7:AF10000").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd

End Sub

Could you tell me how I can modify it so that I can run the code while the sheet is still protected?

Thanks for help

 

[xlbo]

The following must go in the workbook_OPEN event - as mentioned earlier in this thread:

Sheets("SheetName").Protect Contents:=True, userInterfaceOnly:=True

change SheetName to be the name of the sheet you want to perform filters on

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mimitan]

Thanks Goeff! for your reply. I insert the workbook_open event with the line you mentioned, I do still need the codes below as HTH mentioned right?


Sub Macro1()

Sheets("Sheet1").Select
ActiveSheet.Unprotect Password:="password"


'your filter code here

ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

 

[KenWright]

This may help

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

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[xlbo]

mimitan - No - that's the whole point - you DON'T need to unprotect when you use userinterfaceonly - have you actually read the help file ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question