Check each sheet for Auto-filtering and switch it off
 

Hi everyone

This code works on one sheet, but I realise that I actually need it to
look at each sheet in my workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mySht As Worksheet
Dim myRow As Range

Set mySht = Sheets("Sheet1")
Set myRow = mySht.Rows("1:1")

If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
ActiveWorkbook.Save
End If
End Sub


I played around with ( For each sheet ) but could not get the structure
correct.

My sheet array is "Jan" through to "Dec"

Also, I have a Timer control that is triggered after 5 mins of user
sheet inactivity which I would like this to be triggered just prior to
the workbook saving and closing...

As always
Heaps of thanks in advance..

Mick.

Check each sheet for Auto-filtering and switch it off
 

On Feb 9, 11:46*am, Vacuum Sealed wrote:
Hi everyone

This code works on one sheet, but I realise that I actually need it to
look at each sheet in my workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mySht As Worksheet
Dim myRow As Range

Set mySht = Sheets("Sheet1")
Set myRow = mySht.Rows("1:1")

If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
ActiveWorkbook.Save
End If
End Sub

I played around with ( For each sheet ) but could not get the structure
correct.

My sheet array is "Jan" through to "Dec"

Also, I have a Timer control that is triggered after 5 mins of user
sheet inactivity which I would like this to be triggered just prior to
the workbook saving and closing...

As always
Heaps of thanks in advance..

Mick.

Try this:
Dim mySht As Worksheet
Dim myRow As Range

for each mySht in ActiveWorkbook.WorkSheets
Set myRow = mySht.Rows("1:1")
If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
End If
Next mySht
ActiveWorkbook.Save

Check each sheet for Auto-filtering and switch it off
 

On 9/02/2012 9:47 PM, M.Abusido wrote:
Dim mySht As Worksheet
Dim myRow As Range

for each mySht in ActiveWorkbook.WorkSheets
Set myRow = mySht.Rows("1:1")
If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
End If
Next mySht
ActiveWorkbook.Save

Hi

Thx for the pointer

It's works on te first sheet, then halts on the second sheet.

Think it has something to do with myRow not being specific enough to
cover all 12 sheets..

Thx again.
Mick

Check each sheet for Auto-filtering and switch it off
 

This was posted in another NG on Jan 27, 2012...

Sub TurnOffFilterMode()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.FilterMode Then
With wks.UsedRange
.AutoFilter '//turn it off
.AutoFilter '//reset the dropdowns
End With
End If 'wks.FilterMode
Next 'wks
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Check each sheet for Auto-filtering and switch it off
 

Sub test()
Dim mySht As Worksheet
Dim myRow As Range
For Each mySht In ActiveWorkbook.Worksheets
If mySht.AutoFilterMode Then
mySht.Range("A1").AutoFilter
End If
Next mySht
ActiveWorkbook.Save
End Sub


Gord

On Thu, 09 Feb 2012 23:55:54 +1100, Vacuum Sealed
wrote:

On 9/02/2012 9:47 PM, M.Abusido wrote:
Dim mySht As Worksheet
Dim myRow As Range

for each mySht in ActiveWorkbook.WorkSheets
Set myRow = mySht.Rows("1:1")
If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
End If
Next mySht
ActiveWorkbook.Save

Hi

Thx for the pointer

It's works on te first sheet, then halts on the second sheet.

Think it has something to do with myRow not being specific enough to
cover all 12 sheets..

Thx again.
Mick

Check each sheet for Auto-filtering and switch it off
 

If you just want to switch the filter off, then this will do the trick:

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.AutoFilterMode = False
Next



CE



Den 09.02.2012 10:46, Vacuum Sealed skrev:
Hi everyone

This code works on one sheet, but I realise that I actually need it to
look at each sheet in my workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mySht As Worksheet
Dim myRow As Range

Set mySht = Sheets("Sheet1")
Set myRow = mySht.Rows("1:1")

If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
ActiveWorkbook.Save
End If
End Sub


I played around with ( For each sheet ) but could not get the structure
correct.

My sheet array is "Jan" through to "Dec"

Also, I have a Timer control that is triggered after 5 mins of user
sheet inactivity which I would like this to be triggered just prior to
the workbook saving and closing...

As always
Heaps of thanks in advance..

Mick.

Check each sheet for Auto-filtering and switch it off
 

On 10/02/2012 3:18 AM, Charlotte E. wrote:
If you just want to switch the filter off, then this will do the trick:

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.AutoFilterMode = False
Next



CE



Den 09.02.2012 10:46, Vacuum Sealed skrev:
Hi everyone

This code works on one sheet, but I realise that I actually need it to
look at each sheet in my workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mySht As Worksheet
Dim myRow As Range

Set mySht = Sheets("Sheet1")
Set myRow = mySht.Rows("1:1")

If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
ActiveWorkbook.Save
End If
End Sub


I played around with ( For each sheet ) but could not get the structure
correct.

My sheet array is "Jan" through to "Dec"

Also, I have a Timer control that is triggered after 5 mins of user
sheet inactivity which I would like this to be triggered just prior to
the workbook saving and closing...

As always
Heaps of thanks in advance..

Mick.

As always

You guy's are amazing.

To all, thx heaps.

Cheers
Mick.