Macro to Update Leaderboard

Hi

I’m trying to create a ‘live’ Leaderboard for a quiz I want to do with my class.

I’ve created the attached workbook which has two worksheets; Scoreboard and Leaderboard. I intend to Chromecast the Leaderboard only to the class and I’ll be able to see both the Scoreboard and Leaderboard on my computer.

Scoreboard Worksheet:
I’ve set up this worksheet for me to enter the data for each round of the quiz. I’ve entered 1s in various data cells to test the formulae. I will change the names of the teams (Team 1, Team 2 etc) to those provided by the students when I run the competition.
I’ve entered a SUM formula in Column C to calculate the total from each round.
I’ve entered a RANK formula in Column D to determine the rank of each team based on the total.

Leaderboard Worksheet:
I’ve created a Leaderboard that shows only the Team Name and Rank.
First, second and third places get coloured Gold, Silver and Bronze by Conditional Formatting.

What I need:
I need a Macro attached to the Update Leaderboard button on the Scoreboard Worksheet.
So, when I click the Update Leaderboard button, a Macro does two things:
1. Updates the Rank for all teams on the Leaderboard worksheet from column D on the Scoreboard worksheet.
2 Sort the teams on the Leaderboard worksheet by the Rank with the first placed team at the top.

The reason why I want a Macro attached to the Update Leaderboard button, rather than the students seeing it automatically updated when I enter data into the Scoreboard worksheet, is to create suspense.
I’ve tried to create a Macro to do this but have not been successful.

Could someone please help me?

Hi there,

Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:

Option Explicit


Private Sub UpdateLeaderboard()

    Const iNO_OF_COLUMNS    As Integer = 3
    Const iNO_OF_TEAMS      As Integer = 20
    Const iRANK_COLUMN      As Integer = 3
    Const sSHEET_NAME       As String = "Leaderboard"
    Const sFIRST_CELL       As String = "B4"

    Dim rRangeToSort        As Range
    Dim rFirstCell          As Range
    Dim wks                 As Worksheet

    Set wks = ThisWorkbook.Worksheets(sSHEET_NAME)

    Set rFirstCell = wks.Range(sFIRST_CELL)

    With rFirstCell

        Set rRangeToSort = Range(.Cells(1, 1), _
                                 .Cells(iNO_OF_TEAMS, iNO_OF_COLUMNS))

    End With

    With rRangeToSort
        .Sort Key1:=.Cells(1, iRANK_COLUMN), order1:=xlAscending, Header:=xlNo
    End With

End Sub

The highlighted values may be altered to suit your requirements.


The ranking on the Leaderboard worksheet updates automatically because the following formula has been entered in Cell D4 and copied downwards:

=VLOOKUP(B4,  Scoreboard!B4:D23,  3,  FALSE)

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg M

Thank you very much. Your macro works well.

The only change I would request, if it's possible, would be to have the Leaderboard worksheet updated only when the 'Update Scoreboard' button is pushed.

Regards

Alan L 185

Hi Greg M

Thanks for the updated version.

Sorry for causing confusion.

I need a Macro attached to the Update Leaderboard button on the Scoreboard Worksheet.
So, when I click the Update Leaderboard button, a Macro does two things:
1. Updates the Rank for all teams on the Leaderboard worksheet from column D on the Scoreboard worksheet.
2 Sort the teams on the Leaderboard worksheet by the Rank with the first placed team at the top.

Are you able to combined all the coding so that it updates the scoreboard and sorts it into rank order when the 'Update Scoreboard' button is clicked?

Absolutely brilliant! Thank you so much for your help.

All the very best to you.

Alan L 185