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

 

[p45cal]

right-click the tab of the leaderboard sheet and choose 'View Code..'
You'll be plonked into a pane whee there are two dropdown fields.
Choose 'Worksheet from the left one, then 'Activate' from the righ
one.
It should then plonk the cursor between the following two lines:

Private Sub Worksheet_Activate()

End Sub

Inbetween them type the name of your macro.

Close, that newly opened Visual Basic window altogether and, finger
crossed, when you activate that sheet from another sheet (ie. when yo
change to that tab), your sort macro will kick in.

If the sorting is to take place on a sheet other than the leaderboar
tab, it may not work properly, depending on what's in that sort macro
Post again if it goes wrong.

 

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

 

[p45cal]

Unfortunately that won't work. I'm using the "indirect" command t
point to

the dataset on the input worksheet. So the data resides on the inpu tab.

Show us the sorting code, what's in the indirect cells, the name of th
sheet on which the sorting takes place. It shouldn't be a problem.

 

[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
_________________________________

 

[p45cal]

I'm not sure that this isn't more complex than it needs to be but s
that I don't lose any functionality can you tell me the sheet name an
cell address of this formula (and is it just one cell which has
formula like this?)?:
=INDIRECT("Scores!E"& ROW(E3))

Which code module is the code below in? If it's a sheet code module
which sheet?

Private Sub DynSortD(sKeyAddress As String)
Dim sortRange As Range
Dim sKey1 As Range...
...

(If you want, you can PM me at thecodecage with a view to your sendin
me the file to look at. Otherwise where are you posting thes
messages?)