How to create a scoring system using a macro?

[tarekjamil]

I would like to create a macro that adds or deducts points in a table based on the name of the player that was selected. The points are mentioned next to each criteria, meaning that the user selects the player from the dropdown menu and then clicks the add or deduct button. The macro then searches for the name of the player in the score table and then add/deducts the points accordingly.

Criteria	Points	Player Name
Speed	10	Bill Jellen
Strength	20	Tarek Jamil

<tbody>
</tbody>

Player	Points
Bill Jellen	100
Tarek Jamil	150

<tbody>
</tbody>

 

[Fishboy]

I would like to create a macro that adds or deducts points in a table based on the name of the player that was selected. The points are mentioned next to each criteria, meaning that the user selects the player from the dropdown menu and then clicks the add or deduct button. The macro then searches for the name of the player in the score table and then add/deducts the points accordingly.

Criteria	Points	Player Name
Speed	10	Bill Jellen
Strength	20	Tarek Jamil

<tbody>
</tbody>

Player	Points
Bill Jellen	100
Tarek Jamil	150

<tbody>
</tbody>

Hi tarekjamil, welcome to the boards.

How big is the list of players? Is it a long list or a short one?

 

[tarekjamil]

The list of players is long around 50

 

[Fishboy]

The list of players is long around 50

Dang, I had been toying with a very inelegant solution based around a list of 5 or so players. The bigger the list of players got, the more cumbersome and horrible the code became. If there are approx 50 players on your list I will totally have to rethink my approach.

Hopefully someone cleverer than me will come along and help you in the meantime, otherwise it's back to the drawing board for me!

 

[tarekjamil]

Dang, I had been toying with a very inelegant solution based around a list of 5 or so players. The bigger the list of players got, the more cumbersome and horrible the code became. If there are approx 50 players on your list I will totally have to rethink my approach.

Hopefully someone cleverer than me will come along and help you in the meantime, otherwise it's back to the drawing board for me!

Hi Fishboy,
I don't think it should matter if you have 5 or 50 or 100 players. What you need to have is to have the following:
1) add or deduct buttons for each criteria
2) have a value in a table for each criteria (as shown in the post)
3) link the add/deduct button to the value of each criteria and to the cell which has the name of the player (whoever the player is)
4) the macro add/deducts the value (which is linked to the cell) and finds the name of the player (which is also linked to a cell) in a predefined table which has all the scores.

 

[Fishboy]

Hi Fishboy,
I don't think it should matter if you have 5 or 50 or 100 players. What you need to have is to have the following:
1) add or deduct buttons for each criteria
2) have a value in a table for each criteria (as shown in the post)
3) link the add/deduct button to the value of each criteria and to the cell which has the name of the player (whoever the player is)
4) the macro add/deducts the value (which is linked to the cell) and finds the name of the player (which is also linked to a cell) in a predefined table which has all the scores.

Ok, so this still isn't quite how you want it yet, but I am making progress. As mentioned in a previous post I have had to rethink the way I have approached this and so far I have got a score table laid out as below:

Excel 2010

	A	B	C	D
1		Score Up Macro Button	Score Down Macro Button
2	Name	Strength	Speed	Total Score
3	John Lennon			0
4	Paul McCartney			0
5	George Harrison			0
6	Ringo Starr			0
7	Dave Dee			0
8	Dozy			0
9	Beaky			0
10	Mick			0
11	Titch			0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Scoring System​

With the current set up, you simply put values into the score fields for Strength and / or Speed then click either the score up or score down button and the points are added or deducted from the players total at the end of the table. The code for these macros is as follows:

Score Up

Sub Score_Up_All_Boxes()
    Dim rng As Range
    Dim i As Long
' Set the range in column B you want to loop through
    Set rng = Range(Cells(3, "B"), Cells(Rows.Count, "B").End(xlUp))
    For Each cell In rng
' test if cell is empty
        If cell.Value <> "" Then
' write to adjacent cell
            cell.Offset(0, 2).Value = cell.Offset(0, 2).Value + cell.Value
        End If
    Next
' Clears the contents of the newly processed scores
    Range(Cells(3, "B"), Cells(Rows.Count, "B").End(xlUp)).ClearContents
' Set the range in column C you want to loop through
    Set rng = Range(Cells(3, "C"), Cells(Rows.Count, "C").End(xlUp))
    For Each cell In rng
' test if cell is empty
        If cell.Value <> "" Then
' write to adjacent cell
            cell.Offset(0, 1).Value = cell.Offset(0, 1).Value + cell.Value
        End If
    Next
' Clears the contents of the newly processed scores
    Range(Cells(3, "C"), Cells(Rows.Count, "C").End(xlUp)).ClearContents
    MsgBox "Strength and Speed points added to total scores"
End Sub

Score Down

Sub Score_Down_All_Boxes()
    Dim rng As Range
    Dim i As Long
' Set the range in column B you want to loop through
    Set rng = Range(Cells(3, "B"), Cells(Rows.Count, "B").End(xlUp))
    For Each cell In rng
' test if cell is empty
        If cell.Value <> "" Then
' write to adjacent cell
            cell.Offset(0, 2).Value = cell.Offset(0, 2).Value - cell.Value
        End If
    Next
' Clears the contents of the newly processed scores
    Range(Cells(3, "B"), Cells(Rows.Count, "B").End(xlUp)).ClearContents
' Set the range in column C you want to loop through
    Set rng = Range(Cells(3, "C"), Cells(Rows.Count, "C").End(xlUp))
    For Each cell In rng
' test if cell is empty
        If cell.Value <> "" Then
' write to adjacent cell
            cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - cell.Value
        End If
    Next
' Clears the contents of the newly processed scores
    Range(Cells(3, "C"), Cells(Rows.Count, "C").End(xlUp)).ClearContents
    MsgBox "Strength and Speed points deducted from total scores"
End Sub

Eventually I want to work out a way to incorporate your selection of player from a drop-down list and have the score table separate as per your original request, but for the here and now this works. The problem is changing to a drop-down selection and separate score table will mean re-writing the macros. I just wanted to make sure I have the basic mechanics of it down before I start trying to make it more complicated.

 

[tarekjamil]

Hi Fishboy,

Thanks for the work, but there should be two things. 1) A predetermined list that has the points for each criteria (e.g. if I was doing the scoring for a students I would put 10 points for being early, 20 points for completing homework, etc.). So for each criteria I want to put a certain number of points in a table rather than hard-code the points (which I could change If I wanted to) 2) As you said, I want to be able to select the player for which I want to score up or score down.
The initial table in the post might be confusing. Here is a better depiction:

Criteria Points Player Name Dropdown Homework Completion 10 Score Up Macro Button Scroe Down Macro Button Early 20 Score Up Macro Button Scroe Down Macro Button Respectful 30 Score Up Macro Button Scroe Down Macro Button <tbody> </tbody>

<tbody>
</tbody>

Name	Homework Completion	Early	Respectful	Total Score
John Lennon	10	10	10	30
Paul McCartney	20	20	20	60
George Harrison	30	30	30	90
Ringo Starr	10	10	10	30
Dave Dee	20	20	20	60
Dozy	30	30	30	90
Beaky	10	10	10	30
Mick	20	20	20	60
Titch	30	30	30	90

<tbody>
</tbody>

kind regards,