Macro to Update Leaderboard

[Alan L 185]

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?

[Greg M]

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Regards,

Greg M

[Alan L 185]

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

[Greg M]

Hi again,

Many thanks for your feedback.

The attached workbook uses the following code to ensure that the Leaderboard values are updated and sorted only when the "Update" button is pressed:

Please Login or Register  to view this content.

The highlighted values may be changed to suit your requirements.

These values may need to be amended if the layout of the "Scoreboard" worksheet is ever changed.
This value may need to be amended if the layout of the "Leaderboard" worksheet is ever changed.


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

Regards,

Greg M

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

[Greg M]

Hi again Alan,

Please excuse me!

I posted the correct version of the VBA code but attached the wrong version of the workbook

See if the attached version does what you need.

Regards,

Greg M

[Alan L 185]

Absolutely brilliant! Thank you so much for your help.

All the very best to you.

Alan L 185

[Greg M]

Hi again,

Many thanks for your feedback.

You're welcome - glad I was able to help.

Regards,

Greg M


P. S. Many thanks also for the Reputation increase - much appreciated!