Tennis scoring macro

Hi,

This is a bit of a tricky one but....

I want to basically have two buttons in excel - for example - Player A and Player B. I would like to be able to click these buttons and then depending on the number of clicks it allocates a score to another cell. Example, one click on Player A gives a score of 15, another click and it changes to 30 etc etc.
I would like to use this for a complete game of tennis so once we reach the end of one game both scores resort back to 0 and the number of games changes from 1 to 0 etc etc, and then include the set scores.

Any help would be appreciated.

David.

Hi David, welcome to EXcel Forum. A sample worksheet showing which cells are used, and where you want the data, would be helpful. To attach one, just Click Edit Post, then Go Advanced. Scroll down to Manage Attachments and click. Browse for your file, then click Upload. Simple!

ps. Do you have rules for Tie breaks? Number of sets?

see attached file
Here is a rough and ready starter for you to test
Let me have your comments and wish list
I have not include the 5 set option - will add later

This looks to be what i am looking for after testing.

One thing which would be good though is i am looking to create formulas based on the current score which would point to specific cells where the scores would be updating, hence if instead of a pop up the scores were just updated in the cells.

Thanks very much for this.

Wish list:

If when reaching either 3 sets of 5 sets it would revert to game over, and an option to select if it is a 3 set match or 5 set match would also be good.

An option to select if it a singles match or a doubles match would also be good as doubles rules are different see below:

In some doubles matches now (Not grand slam matches), if the score is 1-1 in sets, they now play a Match Tie-Break, which is exactly the same as a normal tie-break yet it is the first to 10 points and whoever wins this wins the match.
Also in these doubles matches there is no advantage, it is just whoever wins the next point when the game is at duece who wins the game.

Also - in grand slams there is no tie-break in the 5th set hence they just keep playing games until a two game margin.

So ideally an extra 5 buttons which can be ticked - 3 or 5 sets? Singles or Doubles? "Match-tie break" (Yes/No), "No Advantage" (Yes/No) and "Tie-Break 5th set" (Yes/No) based on the rules above.

Cheers and sorry if it is getting more complicated

David.

You have now strayed quite a lot from what was in your original post by adding the complications for a doubles match - which makes it a more interesting puzzle.

Before I amend anything, I would like you to confirm that there is nothing else on your list of wants
Also please confirm that you want only the following values transferred to workbook cells:
- current score in points
- current score in games
- current score in sets

I have no idea what you are planning to do with the resultant values, but you may also require some other values which are held in the userform:
- eg is it a singles or doubles match

Ok - I will post an update tomorrow

try this

Tennis.jpg

This is very very good. Has everything i need.

I found one small issue though in relation to the match tie-breaker. This is the same as the normal tie-breaker in that the player has to win by 2 clear points. so 10-9 does not win you the match, it would have to be 11-9 etc. I tweaked the code and it works now.


Thanks again for this. If i have anything else in the future i require i will definitely be in touch

You are welcome - I hope you have fully tested it - I have not.
Some of the code is based on several variables being set to True or False
I have not tried all the permutations - if it gives an odd result, that is where the problem will lie - you will only find it when something peculiar happens

The easiest way to log the scores is to use a change event macro to trigger when changes are made to the scores on the worksheet

Using the file I posted (as amended by you):
- add this code to the VBA module of the sheet containing the scores (must place in the SHEET module)
- add a new worksheet and name it "LogScores" - everything will be listed there
- there are a few duplicate triggers - need to amend userform VBA to sort that - needs some careful thinking - will revisit tomorrow.
Every time the score is changed, the log is also updated

Some values wrapped in CInt() to convert textbox values to numbers to make suppression of zeros easily - will avoid this being necessary when I amend the userform code later

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheets("LogScores")

If Not Intersect(Target, Range("D3")) Is Nothing Then
    r = ws.Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
    With ws.Cells(r, 1)
        On Error Resume Next
        .Offset(, 0).Value = CInt(Range("D2"))
        .Offset(, 1).Value = CInt(Range("D3"))
        .Offset(, 2).Value = CInt(Range("C2"))
        .Offset(, 3).Value = CInt(Range("C3"))
        .Offset(, 4).Value = CInt(Range("B2"))
        .Offset(, 5).Value = CInt(Range("B3"))
        On Error GoTo 0
    End With
    ws.Cells(r, 3).Resize(, 4).NumberFormat = "0;;;@"
    ws.Cells(r, 3).Resize(, 6).HorizontalAlignment = xlCenter
    
End If
End Sub

Either tomorrow, or more likely Sunday, I will post file with amended VBA

Code restructered to make it easier for you to amend
- when game placed summary procedure calls all the other subs
- each sub carries out one task
- sub name makes that pretty clear.

You have not explained where you are heading with this but if there are further developments in mind now is the time to ask for help- before I forget what I've done
From now on, its best if you revise the code - but I am happy to answer any questions.


Hopefully its pretty near the mark.
There are 3 logs
- LogScores = everything
- LogSets = every set
- LogMatches = single line summary of each match


Plus one enhancement - feature allowing games to be played automatically without having to keep pressing the "A" or "B" button - was useful in testing - so have left it for you
- uses random number generator to allocate points to A & B
- my computer simulated hundreds of games with various settings to test that VBA was making correct choices.
- all games played are based on the options selected by user
- suggest you try first with no more than 50 games to see how long it takes
- all games added to the 3 logs

Tennis4.jpg

In PM you asked for a few amendments.
Amendments made
- mainsheet now updated for options selected
- mainsheet now updated for match scores
- pop-up can now be hidden without losing current values
- see Name Manager for all the named ranges created - used to update values in main sheet (allows you to insert rows/columns in main sheet and VBA can keep up with cell reference changes - you may not be aware but if VBA does not re-reference cells automatically unlike Excel)
- score 0-0 shows at beginning of each game
- every points now recorded on sheet LogScores
- columns G & H track cumulative points won in each GAME (goes back to zero at end of game)
- columns I & J track cumulative points won in MATCH (goes back to zero at end of match)

The sheet called "Logscores" is exactly what i want, however. I want to create some formulas based on the values in here yet if i start new game it will no longer work and the formulas disappear.
Essentially what i want is to have the scores in running so something like this:
If games = 1, then my formula will return the values in those two colums: CumPoints-A & CumPoints-B.
This would continue as the game continues, so i would have a list of Game 1, Game 2 etc etc and the points scored in each. I would like these formulas not to need changed so when i do clear results and start a new game they remain.

Any ideas?
Cheers.

1.Test the attached to see if your formulas remain intact.

2. After more than one game, I am not sure what you want. Do you want:
- value on each row to say GAME 01, 02, 03... up to end of match
- value on each row to say SET 1, GAME 01 ... to end of set1, SET2 GAME01 ... to end of set2

3. Would it help to have a worksheet summarising points by game?
or do you prefer everything in the same worksheet?

amended 3 rows in procedure Private Sub cb_ClearHistory_Click()

Instead of deleting all sheet values, now clearing contents of specific columns

        Sheets("LogScores").Columns("A:K").ClearContents
        Sheets("LogSets").Columns("A:C").ClearContents
        Sheets("LogMatches").Columns("A:G").ClearContents

Yes - will post amended workbook tomorrow or next day - cumulative game scores already being captured. Need to:
- add game counter
- add sheet incl update instructions
- include that sheet in Clear Data procedure

sheet "LogGames" added

LogGames.jpg

Perhaps it's time to mark the thread as solved...
(thread tools at top of thread)

Hi,

Nice sheet.

Thank you.