Sort on Tab Change

  • Thread starter Thread starter Joe Schmoe
  • Start date Start date
J

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
 
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.
 
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
 
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.
 
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
_________________________________
 
Back
Top