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

--

Gary Keramidas
Excel 2003


"dhermus" wrote in message
...
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?

On Jul 27, 12:19*pm, "Gary Keramidas" <GKeramidasAtMSN.com wrote:
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

"dhermus" wrote in message

...



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?- Hide quoted text -

- 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.

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

On Mon, 27 Jul 2009 11:28:03 -0700 (PDT), dhermus wrote:

On Jul 27, 12:19*pm, "Gary Keramidas" <GKeramidasAtMSN.com wrote:
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

"dhermus" wrote in message

...



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?- Hide quoted text -

- 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.