Challenge ladder setup

robfosters

New Member
Joined
Aug 21, 2007
Messages
28
Has anyone had any experience of setting up a challenge ladder on excel?

Basically, i've got 90 players on a ladder. When one beats the other they replace them in their position and the beaten player moves down one.

Has anyone got any idea how I can set it up so that I have got a box come up with players names in a drop down box. I can then select the player on the left who has won, and the player on the right who has lost, enter it and the ladder to automatically update.

I'm not being lazy, I just have not got a clue how to do this and I really cant get my head around the tech books.

Any help is greatly appreciated.

Rob
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

robfosters

New Member
Joined
Aug 21, 2007
Messages
28
Bump,

Please, I desperately need help on this one. I have looked everywhere but am still none the wiser.

If no-one understands what I mean, just say so and i'll clarify.

If anyone couldpoint me towards a tutorial on the subject, that would help, as I have been unable to find anything.
 

robfosters

New Member
Joined
Aug 21, 2007
Messages
28
Can anyone tell me if the front end where you input the results needs to be done with Visual basic?
 

bruty

Active Member
Joined
Jul 25, 2007
Messages
445

ADVERTISEMENT

Can anyone tell me if the front end where you input the results needs to be done with Visual basic?

You should just be able to do the 'front end' using either cells where you input the names, or drop downs in cells to select them.

No idea of the code behind it to make them switch though.
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
I've got a solution worked up for you. Instead of using dropdown boxes though, I have you click the cell containing the name of the player and hit a commandbutton labeled "Win" or "Lose" as appropriate. The sheet includes three named ranges, four buttons (win, lose, reset, and excecute), and each button has a macro. I made the following assumptions:

Row 1 is a header row
Column A is player rank numbers
Column B is player names

I used column C for the W or L resulut of the current game
Column D totals up the wins
Column E totals up the losses

Columns F and G contain the re-ranking formulas, and should be hidden once the sheet works. They look wrong except when you've chosen both a winner and a loser.



Named ranges:

A range named "players", which includes all the names in column B. This formula will accommodate any number of players, so if your group shrinks or grows this will still work. Insert\Name\Define, use this formula:
Code:
=OFFSET(Sheet1!$B$1,1,0,COUNT(Sheet1!$A:$A),1)

A range named "newranks":
Code:
=OFFSET(Sheet1!$F$1,1,0,COUNT(Sheet1!$A:$A),1)

And a range named "newplayers":
Code:
=OFFSET(Sheet1!$G$1,1,0,COUNT(Sheet1!$A:$A),1)

These names are used both in the macros and on the sheet.


The macros:
Create four buttons, in this order:
CommandButton1 will be the "Win" button
CommandButton2 will be the "Loss" button
CommandButton3 will be the "Reset" button
CommandButton4 will be the "Excecute" button

Asign the following code to those buttons:
Code:
Public winnerrow As Long
Public loserrow As Long
'Code created by mrexcel.com member gardnertoo


Private Sub CommandButton1_Click()

'Choose and highlight the winner

With Selection
    Set isect = Application.Intersect(Range("players"), ActiveCell)
    If isect Is Nothing Then
        MsgBox ("Select a cell in the 'Player' column")
    Else
        .Interior.ColorIndex = 4  '4 = green = win
        Cells(.Row, .Column + 1).Value = "W"
        Cells(.Row, .Column + 2).Value = Cells(.Row, .Column + 2).Value + 1
        winnerrow = .Row
        CommandButton1.Visible = False
        CommandButton3.Visible = True
        
    End If

End With


End Sub

Private Sub CommandButton2_Click()

'Choose and highlight the loser

With Selection
    Set isect = Application.Intersect(Range("players"), ActiveCell)
    If isect Is Nothing Then
        MsgBox ("Select a cell in the 'Player' column")
    Else
        .Interior.ColorIndex = 3  '3 = red = loss
        Cells(.Row, .Column + 1).Value = "L"
        Cells(.Row, .Column + 3).Value = Cells(.Row, .Column + 3).Value + 1
        loserrow = .Row
        CommandButton2.Visible = False
        CommandButton3.Visible = True
        
    End If

End With



End Sub

Private Sub CommandButton3_Click()

'Reset winner and loser, re-show the buttons

'Un-highlight
    With Range("players")
        .Interior.ColorIndex = 0
        .Offset(0, 1).Value = ""
    End With

If winnerrow = 0 Then GoTo showbuttons
If loserrow = 0 Then GoTo showbuttons

'Back out the additional win and loss
    Cells(winnerrow, 4).Value = Application.Max(0, Cells(winnerrow, 4).Value - 1)
    Cells(loserrow, 5).Value = Application.Max(0, Cells(loserrow, 5).Value - 1)
    
showbuttons:

'Show the "Win" and "Loss" buttons again
    CommandButton1.Visible = True
    CommandButton2.Visible = True
    CommandButton3.Visible = False
        
End Sub

Private Sub CommandButton4_Click()

'Move the rankings, unhighlight, re-show the buttons

If winnerrow < loserrow Then
    MsgBox ("No change in rankings")
Else
    'Winner takes loser's spot
    'Loser, and everyboy above the winner, moves down one
    Range("players").Value = Range("newplayers").Value
    Range("wins").Value = Range("newwins").Value
    Range("losses").Value = Range("newlosses").Value
    
End If

'Un-highlight
    With Range("players")
        .Interior.ColorIndex = 0
        .Offset(0, 1).Value = ""
    End With

'Show the "Win" and "Loss" buttons again
    CommandButton1.Visible = True
    CommandButton2.Visible = True
    CommandButton3.Visible = False
    
End Sub
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938

ADVERTISEMENT

Here's what it looks like, with a winner and loser selected but not excecuted:
Book1
ABCDEFG
1RankPlayerThis Game ResultWinsLosses(Hide Me) New Rank(Hide Me) New List
21Adam001Adam
32Bob002Bob
43CarolL014Franny
54Denise005Carol
65Eric006Denise
76FrannyW103Eric
87George007George
98Harry008Harry
Sheet1
 

robfosters

New Member
Joined
Aug 21, 2007
Messages
28
Thanks very much for that, but how do I create macro buttons and insert the code. I'm afaraid I really am that much of a novice. Once I get the macro's sorted, I should be there.

Thx
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
From the Excel main menu, select View\Toolbars\Control Toolbox. This gives you a bunch of tools you can embed in the sheet, one of which is the Command Button. Click the command button icon, then draw the button on the worksheet whatever size and location you want. RIGHT click the new button to call up the Properties menu. Change the caption (default is CommandButton1). Repeat for the other three buttons. Do NOT close the Control Toolbox yet.
Once all four buttons are looking and located where you want, go to the tab at the bottom of the worksheet (Sheet1) and RIGHT click, then select "View Code". In the window that launches copy and paste (all in one shot) all the code in my previous post. Close that window.
In the Control Toolbox, there is an icon that looks like a 45°-45°-90° triangle with a pencil and a ruler. This is the "Design Mode" button, and when you created your first button, it became selected. De-select it now. This takes you out of the button edit mode and activates them.
Once you have a few rows of data entered, everything should work. I built the macro behind the "Win" and "Loss" buttons so you can select only one winner and only one loser. If you chose the wrong one, hit the "Reset" button to clear your choices and restore the buttons.
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
For the benefit of other users reading this thread:

Rob and I went off-line and got this working for him. I have edited the code above to correct some issues he found -- it's good as of Aug 23 2007, 2:00 pm Seattle time. There is now a column H for new wins and a column I for new losses. Four new named ranges were built: wins, losses, newwins, and newlosses. These work exactly like players and newplayers do.

The "Reset" button (Commandbutton3) is now visible only when you have started making winner and loser selections which might need to be un-done, but have not yet hit the "Excecute" button (Commandbutton4). It was causing lots of spurious deletions when it was active at other times because of how I handled it's row-finding.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top