Clearing all autofilters on protected file

[KGee]

I have a spreadsheet that gets protected before it is sent out to staff. It uses AutoFilters which I want to keep enabled while the spreadsheet is locked. Since there are quite a few columns, I added a button to clear all of the filters so they don't have to click each one individually. The problem is my button only works when the sheet/workbook are not protected.

I get a run-time 1004 error when it is protected:
"ShowAllData method of Worksheet class failed"

  If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
  End If

Is there another option I can use?

 

[KGee]

Looks like I may have to unprotect, clear filter, then reprotect. Can anyone confirm that is the only option other than removing the button and letting them clear each filter individually.

 

[Smitty]

If you have Excel 2000 or above, you have the option of allowing autofilter on a protected sheet.

Otherwise you can add: ActiveSheet.Unprotect at the beginning of your button code and reprotect at the end.

HTH,

 

[Boa_2K9]

Firstly, I'm by no means a VBA expert. I've accomplished much of my coding by looking things up online and modifying code I found to suit my needs. Thanks to all you accomplished coders for that! I think I have what you need thoiugh.

You can avoid having to unprotect the sheet to clear auto-filters by protecting your sheet via VBA code as opposed to protecting from the menu-bar. The key is "UserInterfaceOnly:= True", which can only be entered via VBA code, as far as I know. This allows macros to work within a protected sheet. I just entered it in a new module and assigned a hotkey to the macro. Then, set filters on unprotected sheet and execute protect macro. The macro I use below:


Sub PrSh()
'
' PrSh Macro
'
' Keyboard Shortcut: Ctrl+y
'
Worksheets("Sheet1").Protect Password:="yourpassword", _
Contents:=True, Scenarios:=True, _
AllowFiltering:=True, UserInterfaceOnly:=True

I created it by first recoding a macro by protecting my sheet from menu-bar with the options I wanted. I then simply added the "UserInterfaceOnly:=True" to the end of the recorded macro. You can also apply this code to a CommandButton on your sheet if you prefer that over a keyboard-shortcut. Hope this helps.

Not sure the above code will work on a Shared Workbook though. Shared Workbooks seem to introduce a whole new set of issues. I'm currently trying to figure out how to allow Activesheet.ShowAllData to work on a protected, Shared Workbook from a CommandButton. (search forums with "ShowAllData Error" keywords for my post on this problem..thanks in advance for any help there)

 

[deedledoo]

I know this answer comes a little late but since I was still searching for a solution and this came up near the top of the results I figured I'd post my solution too. Like KGee I'm trying to clear any Autofilters from a protected file. I don't know the password and it is not enabled to allow me to adjust the existing filters. So I just put in new filters, for some reason I'm allowed to do this.

Range("A1:A2").AdvancedFilter Action:=xlFilterInPlace, Unique:=False

Hope this helps anyone else in the future.