Create Macro/Button that will return all autofilters back to ALL

mprusak

New Member
Joined
May 5, 2003
Messages
36
Sorry if this has been covered, I tried to search real quick but didn't find anything.

I want to have a small button at the top of the sheet that when pressed, will automatically reset all filtered autofilters back to show all.

I'm hoping this will be fairly simple.

Thanks in advance for any help.

Mark.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try:

Sub ShowAll()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
If .FilterMode Then .ShowAllData
End With
Next wks
End Sub

JT
 
Upvote 0
If the button is to help other that are not very familiar with Excel then JT's code should work (I didn't test it, but looks good to me). However, if the button is for yourself, I would just memorize the keystroke Alt+D,F,S. Normally, that's gonna a lot quicker than trying to mouse and click on a button.
 
Upvote 0
Works great...thanks to both for the quick response. Yes it's for other user's benefit.

Thanks again.

Mark.
 
Upvote 0
sorry, one more thing...

if I protect the sheet it no longer works...how do I get around that?

thanks again.
 
Upvote 0
well...

I was thinking something like this:

.Protect Password:="dor", UserInterfaceOnly:=True

but, since it didn't work, obviously not. Since this is my first attempt at VBA code I really have no understanding of what I am doing...just copying and pasting what I can find online.

any hints? :)

thanks.

Mark
 
Upvote 0
Welp, you got the cherry pie filling and the crust is beautiful. If you'd'a just remembered the pie plate, you'd'a had yerself a right nice pie. :wink:

That ".protect" needs an object - it's a verb in search of a noun. In this case a worksheet object. Try figuring out how to tell it which worksheet to protect.

note to self: quit using food metaphors - ya make yerself hungry.
 
Upvote 0
Ohhhh, I hate that 10-minute edit lockout... :mad:

I should have mentioned -- just to set yer mind at ease -- I answered the way I did because you did a great job in correctly identifying what you needed to do. If you set protection w/ UIO:=TRUE, you can still manipulate autofilter settings using VBA.
 
Upvote 0
well...

I was thinking something like this:

.Protect Password:="dor", UserInterfaceOnly:=True

but, since it didn't work, obviously not. Since this is my first attempt at VBA code I really have no understanding of what I am doing...just copying and pasting what I can find online.

any hints? :)

thanks.

Mark
This will take care of the protected sheet.
The code goes in Worksheet module.
Change Sheet name or range to fit your need.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
With Worksheets("Sheet3")
.Protect Password:=Pass, _
DrawingObjects:=False, Contents:=False, Scenarios:=False, UserInterfaceOnly:=False
If Not .AutoFilterMode Then
.Range("A5:F5").AutoFilter 'Where AutoFilter Applied'
End If
.EnableAutoFilter = True
End With
With ActiveSheet
.Protect Password:=Pass, _
DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
Application.ScreenUpdating = True
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top