Preventing Filters

[ScabbyDog]

Is there vba code that will prevent auto- filters being selected in a worksheet/workbook as I've other macro's that run automatically that don't work well when filters are active.

[Leith Ross]

Hello ScabbyDog,

You can turn auto-filtering off at any time with VBA.

Please Login or Register  to view this content.

[ScabbyDog]

Thanks, but I need code that when someone attempts to turn on AutoFilters on a worksheet, it disables it immediately.

[ScabbyDog]

Any ideas if possible?

[GeneralDisarray]

Adding filters doesn't trigger an event that I know of (which you could make use of the run an unfilter macro).... BUT n a nutshell, if you need to prevent the user from doing what they want to the sheet, you just protect the sheet.

[ScabbyDog]

Adding filters doesn't trigger an event that I know of (which you could make use of the run an unfilter macro).... BUT n a nutshell, if you need to prevent the user from doing what they want to the sheet, you just protect the sheet.

How do you protect the sheet to prevent an autofilter being applied though?

[rkey]

it is under View -> Protect Sheet -> Use Autofilter.
Here you can allow/disallow users to use the Autofilter

[ScabbyDog]

Thanks. How do I put that into vba code though so whenever I run a macro, it disables the protect sheet for AUtoFilter only, and then at the end of the macro, it re-enables it.

[ScabbyDog]

I can use the VBA recorder for that, however when I do this it locks all the cells as well. I just want the AutoFilter to be blocked. Any idea how to alter the below so it does just that?

Please Login or Register  to view this content.

[GeneralDisarray]

Ok - you just need to protect/unprotect at the start and end. I would recommend using a password. Also, you don't have to include all of the options (DrawingObjects:=False, Contents:=True, etc....). You set those manually in the ribbon, one time. When your code re-protects the sheet it will remember the choices you made.

TO PREVENT FILTERS just protect the sheet (by default, the box for 'Use Autofilter' is unchecked... but if you want to verify that, when you protect the sheet from the ribbon scroll down the list of optional allowed actions to see it).


Example:

Please Login or Register  to view this content.

The most important thing to remember is to set up the worksheet properly so the user can access the areas they should be able to access - and make sure you unprotect/re-protect the sheet in any macro that needs to alter the protected sheet.

[ScabbyDog]

Thanks.

I want all users to be able to edit everything except use autofilter.

What should be ticked and unticked in that list so?

[GeneralDisarray]

... check whatever you want them to be able to do ...

If that is the only thing you want to do, try this:

• Highlight the cells the user will need to have access to.
• Right click anywhere in the selected area and select 'Format cells'.
• Go to the last tab named 'Protection'.
• Uncheck the first box labeled 'Locked'.
• Press OK.

When you lock the sheet, check any of the boxes you want - you can check all of them, except the 'use autofilter' if you like.

Does that work for you?