Sort on Tab Change

[Joe Schmoe]

Is there a way to trigger sorting of a column, when I change tabs? I have a
dataset that I use for input and a different tab which is a leaderboard. I
want to sort the leaderboard by high score every time I change to that tab.
I currently have a macro button I click to sort by high score, then I have
to click the leadboard tab. I'd obviously like to click the leaderboard tab
and have the data sorted by high score. Just attempting to combine 2 steps.

step 1 - Sort input form by highscore
step 2 - open the leaderboard tab



Thanks,


Jeff

 

[Gary Brown]

Put something like this in the Leaderborad Worksheet module...

Private Sub Worksheet_Activate()
Range("A:C").Sort Key1:=Range("C2"), _
Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Where the range is the columns desired and the key is the column where the
scores are.

 

[Joe Schmoe]

The problem is the sort has to be done on the data input tab before the
macro can run. It has to happen in the following order.
1. On the Input tab - sort by highscore
2. Open leaderboard tab

Thanks again

 

[Joe Schmoe]

Unfortunately that won't work. I'm using the "indirect" command to point to
the dataset on the input worksheet. So the data resides on the input tab.

 

[jeffatwork]

Here is the reference to the Input form named "Scores"
=INDIRECT("Scores!E"& ROW(E3))


Here is the sort:

Private Sub Worksheet_Activate()
DynSortD "E3"
End Sub


Private Sub DynSortD(sKeyAddress As String)
Dim sortRange As Range
Dim sKey1 As Range
Dim lastRow As Long
Const TestCol = "D"
Const firstColToSort = "A"
Const lastColToSort = "O"
Const firstRowToSort = 3

lastRow = Range(TestCol & Rows.Count).End(xlUp).Row
If lastRow < firstRowToSort Then
Exit Sub ' nothing to sort
End If
Set sortRange = Range(firstColToSort & firstRowToSort & ":" &
lastColToSort & lastRow)
Set sKey1 = Range(sKeyAddress)

sortRange.Sort Key1:=sKey1, Order1:=xlDescending, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End Sub
_________________________________