remove autofilter on close of workbook

[dhermus]

I want to automatically turn off autofilter when a workbook is
closed. How do I incorporate "autofiltermode=false" into a macro to
accomplish this when a workbook with multilple worksheets is closed?

 

[Gary Keramidas]

see if this works. put it in the thisworkbook object. it should check every
sheet and turn off the autofilter

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim i As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
Next

end sub

 

[dhermus]

see if this works. put it in the thisworkbook object. it should check every
sheet and turn off the autofilter

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim i As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
Next

end sub

--

Gary Keramidas
Excel 2003






- Show quoted text -

This worked, with one unexpected twist, the worksheets are password
protected. I will have to unprotect and protect on each side of this
statement.

 

[Gord Dibben]

Same password all sheets?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim i As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="justme"
If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
ws.Protect Password:="justme"
Next ws
End Sub


Gord Dibben MS Excel MVP