remove autofilter on close of workbook
 

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?

remove autofilter on close of workbook
 

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?

remove autofilter on close of workbook
 

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.

remove autofilter on close of workbook
 

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.