Run Macro when close spreadsheet

D

davemel

I am trying to create a macro that will will remove all filters using the
data - filters - show all option from a spreadsheet when exiting the
spreadsheet. I have a Macro called Auto_Close. This is ok when there are
filters to remove but not when there are none to remove (i.e the data -
filters - show all option is greyed out when there are no filters). How could
i get around this ?

Thanks
 
D

Dave Peterson

The bad news is that you'd have to save the workbook in that state, too. And
the user may not want any changes that they've made to be saved.

I think I'd use Auto_open() instead of Auto_Close() instead. It'll run each
time the workbook is opened and set it up nicely for that session.

One way is to ignore any error that may occur:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
wks.ShowAllData
Next wks
On Error GoTo 0
End Sub

Another way is to check to see if things need to be reset first:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If wks.AutoFilterMode Then
If wks.FilterMode Then
wks.ShowAllData
End If
End If
Next wks
End Sub
 
Top