remove autofilter on close of workbook

  • Thread starter Thread starter dhermus
  • Start date Start date
D

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?
 
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
 
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 -
Click to expand...

This worked, with one unexpected twist, the worksheets are password
protected. I will have to unprotect and protect on each side of this
statement.
 
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
 
Back
Top