Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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>


  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    see wath error have...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Insert the line

    Set WS = ActiveSheet

    below

    Dim WS As Worksheet

    or use sdckapr's suggestion.

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Try this:

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

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    no words!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •