if existis a filtered column ... (2000 sr 1)

[sal21]

please adjust this, in effect in evenet click of sheet, if is filtered one or mor column in the sheet showalldata:
Private Sub Worksheet_Activate()
Range("A1").Select
Call LEVA_FILTRO
End Sub
Sub LEVA_FILTRO()
Dim WS As Worksheet
If WS.AutoFilterMode = True Then
WS.ShowAllData
End If
End Sub

[HansV]

Can you tell us what the problem with the code is? Do you get an error message, or does the code do something different than you want, or ...?

[sdckapr]

Are you looking for this?
You need to tell excel which worksheet to work on, you never "set WS" in the second sub.
Steve

<pre>Private Sub Worksheet_Activate()
Range("A1").Select
Call LEVA_FILTRO(Me)
End Sub
Sub LEVA_FILTRO(WS As Worksheet)
If WS.AutoFilterMode = True Then
WS.ShowAllData
End If
End Sub</pre>

[sal21]

see wath error have...

[HansV]

Insert the line

Set WS = ActiveSheet

below

Dim WS As Worksheet

or use sdckapr's suggestion.

[sal21]

work only if a filter is activate in the sheet...
if not are filtre go in error: 1004

last code:
Private Sub Worksheet_Activate()
Range("A1").Select
Call LEVA_FILTRO
End Sub
Sub LEVA_FILTRO()
Dim WS As Worksheet
Set WS = ActiveSheet
If WS.AutoFilterMode = True Then
WS.ShowAllData
End If
End Sub

[sal21]

work only when i use FilterMode inested AutoFilterMode are different the 2 command?

Private Sub Worksheet_Activate()
Range("A1").Select
Call LEVA_FILTRO
End Sub
Sub LEVA_FILTRO()
Dim WS As Worksheet
Set WS = ActiveSheet
If WS.FilterMode = True Then
WS.ShowAllData
End If
End Sub

[HansV]

Try this:

Sub Leva_Filtro()
With ActiveSheet
If .AutoFilterMode = True Then
If .FilterMode = True Then
.ShowAllData
End If
End If
End With
End Sub

[sal21]

Sure this is a professional solution...
You have used the "if" statement for two possibility! or not understand?
Good.

[HansV]

The code first tests if there is an autofilter (If .AutoFilterMode = True Then), and if that is the case, it tests if the worksheet is currently filtered (If .FilterMode = True Then). Only if both conditions are true, ShowAllData is executed.

[sal21]

no words!

[sdckapr]

Isn't that redundant? I thought if Filtermode is true then Autofiltermode must be true?

If you just test for filtermode you don't need to test for autofiltermode...

Steve

[HansV]

FilterMode is true if the worksheet is filtered, whether through AutoFilter or through Advanced Filter. So FilterMode can be True while AutoFilterMode is False. But I agree that the test is superfluous.