+ Reply to Thread
Results 1 to 5 of 5

Excel VBA AUtofilter arrow (blue) on wrong column

  1. #1
    Registered User
    Join Date
    02-16-2005
    Posts
    5

    Excel VBA AUtofilter arrow (blue) on wrong column

    Hi everybody,
    I'm having a bit of a struggle to find out what's wrong with this code:

    VBA CODE:
    Private Sub Worksheet_Activate()
    Dim rFilter As Range

    On Error Resume Next

    'turn on autofilter
    Me.EnableAutoFilter = True
    Set rFilter = Me.Range("tl_Transactions_Data")
    rFilter.AutoFilter
    If ActiveSheet.FilterMode Then
    Me.ShowAllData
    End If

    'toggle drop-down arrows
    If Not Me.AutoFilterMode Then rFilter.AutoFilter

    Me.EnableOutlining = True

    Me.Protect Password:="My_Sheet", Contents:=True, UserInterfaceOnly:=True, DrawingObjects:=False

    'sort the transaction data range -no headers!
    Me.Range("tl_transaction_data").Sort _
    Key1:=[A1], Order1:=xlAscending, header:=xlNo, Orientation:=xlSortColumns

    If Application.ScreenUpdating = False Then Application.ScreenUpdating = True

    If Not Application.Calculation = xlCalculationAutomatic Then Application.Calculation = xlCalculationAutomatic

    End Sub

    VBA CODE

    When the autofilter is enabled it displays the blue arrow on a different column, making things very difficult (for me as the developer ).

    Any ideas, I couldn't find anything on this on the web..

    Thank you very much,

    bastanu

  2. #2
    Jim Cone
    Guest

    Re: Excel VBA AUtofilter arrow (blue) on wrong column

    b,
    And the correct column is ?
    Jim Cone
    San Francisco, USA


    "bastanu"
    <[email protected]>
    wrote in message
    news:[email protected]...

    Hi everybody,
    I'm having a bit of a struggle to find out what's wrong with this
    code:
    VBA CODE:
    Private Sub Worksheet_Activate()
    Dim rFilter As Range
    On Error Resume Next
    'turn on autofilter
    Me.EnableAutoFilter = True
    Set rFilter = Me.Range("tl_Transactions_Data")
    rFilter.AutoFilter
    If ActiveSheet.FilterMode Then
    Me.ShowAllData
    End If
    'toggle drop-down arrows
    If Not Me.AutoFilterMode Then rFilter.AutoFilter
    Me.EnableOutlining = True
    Me.Protect Password:="My_Sheet", Contents:=True,
    UserInterfaceOnly:=True, DrawingObjects:=False
    'sort the transaction data range -no headers!
    Me.Range("tl_transaction_data").Sort _
    Key1:=[A1], Order1:=xlAscending, header:=xlNo,
    Orientation:=xlSortColumns
    If Application.ScreenUpdating = False _
    Then Application.ScreenUpdating = True
    If Not Application.Calculation = xlCalculationAutomatic Then
    Application.Calculation = xlCalculationAutomatic
    End Sub

    VBA CODE
    When the autofilter is enabled it displays the blue arrow on a
    different column, making things very difficult
    (for me as the developer ).
    Any ideas, I couldn't find anything on this on the web..
    Thank you very much,
    bastanu

  3. #3
    Registered User
    Join Date
    02-16-2005
    Posts
    5
    Jim,
    The arrow for the filtered column (criteria=x) should be blue, but instead the 9th column to the left is turning blue, while the filtered one stays black. So if you filter one of the first eight no blue, the ninth turns the first arrow blue and so on.
    b

  4. #4
    Jim Cone
    Guest

    Re: Excel VBA AUtofilter arrow (blue) on wrong column

    bastanu,

    I also thought maybe you would let us know what the named range encompassed.
    However, there is no filtering in your code and the filter arrow doesn't change
    color until that is done. So ...

    If you comment out the "on error resume next" line and run your code
    the sort method will fail.

    If you replace...
    "Me.Range("tl_transaction_data").Sort
    with
    "rFilter.Sort"
    then the sort method works.

    Also, for some reason known only to Microsoft and maybe not even them,
    using the Sort Orientation constants require you to be in a different universe.

    xlLeftToRight = 2
    xlTopToBottom = 1
    while...
    xlSortRows = 2
    xlSortColumns = 1

    If you want to sort columns, use a constant with a value of 2.
    Your sort code, when working, sorts rows not columns.

    See if the above changes, help with your problem.

    Regards,
    Jim Cone
    San Francisco, USA


    "bastanu"
    <[email protected]>
    wrote in message
    news:[email protected]...

    Jim,
    The arrow for the filtered column (criteria=x) should be blue, but
    instead the 9th column to the left is turning blue, while the filtered
    one stays black. So if you filter one of the first eight no blue, the
    ninth turns the first arrow blue and so on.
    b--
    bastanu


  5. #5
    Registered User
    Join Date
    02-16-2005
    Posts
    5
    Jim,

    Thank you very much for your help!

    I used the rFilter.Sort instead of Me.Range("tl_transaction_data").Sort as you suggested and the sort works. The code does not apply filters on Activate, it simply allows them to be applied later as needed. So now when I apply a filter on a column the arrow turns blue as it should.

    The range sorted has quite a few columns (transaction entry), and I am sorting the entire range (transaction = row) based on the transaction id (number stored in column A), so I'm not quite sure what you mean by "your sort is sorting rows".

    Thanks again,
    b

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1