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

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 right
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, fingers
crossed, when you activate that sheet from another sheet (ie. when you
change to that tab), your sort macro will kick in.

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196249

http://www.thecodecage.com/forumz

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.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Joe Schmoe" wrote:

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



.

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


"p45cal" wrote in message
...

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 right
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, fingers
crossed, when you activate that sheet from another sheet (ie. when you
change to that tab), your sort macro will kick in.

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=196249

http://www.thecodecage.com/forumz

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.


"Gary Brown" <junk_at_kinneson_dot_com wrote in message
...
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.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Joe Schmoe" wrote:

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



.

Joe Schmoe;701542 Wrote:

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.




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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196249

http://www.thecodecage.com/forumz

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" wrote in message
...

Joe Schmoe;701542 Wrote:

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.




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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=196249

http://www.thecodecage.com/forumz

I'm not sure that this isn't more complex than it needs to be but so
that I don't lose any functionality can you tell me the sheet name and
cell address of this formula (and is it just one cell which has a
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 sending
me the file to look at. Otherwise where are you posting these
messages?)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196249

http://www.thecodecage.com/forumz