Setting back the autofilter to 'all'

[VILLABILLA]

Hi,

I have a worksheet that has the autofilter working on two columns;
and B.

It is important that when the user is done with viewing the sheet tha
he or she puts back the autofilter to 'all' so that all data i
visible, otherwise I get a mix up with macro that is in work.

This macro pastes data on the sheet, if the autofilter has sorte
something at the same moment, I get an error.

I can warn them to set back the autofilter before they leave the shee
but I already know that it is gonna be an issue because they wil
sometimes forget to do that...

Is there a way to get the autofilter to go back automatically when the
leave the worksheet?

If above asked is not possible, an alternative option would be to pu
in an error message box when the macro goes wrong that tells the use
to change back the autofilter.

I hope I'm not asking to much...

Thanks in advance

 

[Ron de Bruin]

Hi VILLABILLA

You can use this event in the sheet module maybe

Private Sub Worksheet_Deactivate()
Me.AutoFilterMode = False
End Sub

 

[AlfD]

Robby:

I suggest you don't rely on users to do the right thing.

Maybe you should do the safety job in your pasting macro: have it rese
the autofilter to what you want. (References to autofilter in Help)

Al

 

[Bob Phillips]

This might be one way to do it

On Error Resume Next
stradd = ActiveSheet.AutoFilter.Range.Address
If Err.Number = 0 Then
Range(stradd).AutoFilter
End If
On Error GoTo 0

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

Hi

After reading Bob's reply I see I misunderstood you

--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi VILLABILLA

You can use this event in the sheet module maybe

Private Sub Worksheet_Deactivate()
Me.AutoFilterMode = False
End Sub

 

[Dave Peterson]

One more way:

With ActiveSheet
If .FilterMode Then
.ShowAllData
End If
End With

or just:

on error resume next
activesheet.showalldata

 

[VILLABILLA]

I'm very impressed... first of all with all your solutions and secondl
with the enormous amount of possibilities Excel has...

Thanks a lot