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


--
bastanu
------------------------------------------------------------------------
bastanu's Profile: http://www.excelforum.com/member.php...o&userid=20015
View this thread: http://www.excelforum.com/showthread...hreadid=395632

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


"bastanu"

wrote in message
...

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

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


--
bastan
-----------------------------------------------------------------------
bastanu's Profile: http://www.excelforum.com/member.php...fo&userid=2001
View this thread: http://www.excelforum.com/showthread.php?threadid=39563

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"

wrote in message
...

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

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


--
bastanu
------------------------------------------------------------------------
bastanu's Profile: http://www.excelforum.com/member.php...o&userid=20015
View this thread: http://www.excelforum.com/showthread...hreadid=395632