add player score to team score

[breilly00]

I am trying to create a score sheet to display on our external monitor during our pool game. We have 2 teams of 4 players each. When a player 1, on team 1 scores points we want to enter the player score and have that score added to the team score. When player 2, on team 1 shoots their score is added to the team score. This continues under either team 1 or team 2 reaches the number of game winning points. The problem I have is when player 1 score is entered it needs to be added to the total and then not added to the total when player 2 shoots and their score is added to the total.

How can I set it up so when the next player plays and their score is entered it does not include the previous players score. I would like to erase the previous players score when the current players score is entered so only the last players score is displayed. Also a nice to have will be to be able to identify (highlight the players name) the player who is up next once the current players score is entered. Eg. Player 4 is identified that it is their turn when player 3 score is entered. and the player 1 is highlighted when player 4 score is entered.

For example

Team 1
Player Score
Player 1 X
Player 2 X
Player 3 X
Player 4 X

Team 1 score XXXX

Team 2
Player Score
Player 1 X
Player 2 X
Player 3 X
Player 4 X

Team 2 score XXXX

 

[My Aswer Is This]

This surely can be done with Vba

Give me more specifics like.
Where on your worksheet will you have Player names

So how about putting John in Range A2
And when you double click on A2 the cell in Range B2 will receive the number 1
Assuming ever time the score is just a number and not a X
The next time John scores B2 will be advanced by 1 and will now be a 2

That will show Johns total score
Now a number will also be added to the total Team score which will be shown in Range G2 each time John Scores

And this would be repeated on down Column A for each Team Member

Now you would have to have Team 1 members in ranges A2 To A5
And team members for Team 2 would be in ranges A7 To A10
Team 1 total score would be shown in range G2 and Team 2 Total score would be shown in H2

Now we could also include your need for highlighting next Team member who should shoot and who would be next member to shoot

So would something like this work?
If not give me your ideal.

 

[breilly00]

Yes, your approach makes a lot of sense to me. I believe that I can take your idea and begin the VBA now. Thank you very much for getting me started

 

[My Aswer Is This]

Yes, your approach makes a lot of sense to me. I believe that I can take your idea and begin the VBA now. Thank you very much for getting me started

If you know how to do this that's great. If you need some help let me know. I wrote a script that works but if you know how to do it that's great
Show me your script when done I would like to see how you did it.

 

[My Aswer Is This]

Here is the script I put together:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Modified 3/15/2020 9:37:12 PM EST
Select Case Target.Address
Case "$A$2", "$A$3", "$A$4", "$A$5"
Target.Offset(, 1).Value = Target.Offset(, 1) + 1
Range("G2").Value = Range("G2") + 1
Range("A2:A5").Interior.Color = xlNone
Target.Interior.Color = vbGreen
Target.Offset(1).Interior.Color = vbRed
Range("F1").Value = "Start over"
       
Case "$A$7", "$A$8", "$A$9", "$A$10"
Target.Offset(, 1).Value = Target.Offset(, 1) + 1
Range("H2").Value = Range("H2") + 1
Range("A7:A10").Interior.Color = xlNone
Target.Interior.Color = vbGreen
Target.Offset(1).Interior.Color = vbRed
Range("F1").Value = "Start over"

Case "$F$1"
Range("A2").Resize(12).Interior.Color = xlNone
Range("B2:B10").Clear
Range("G2:H2").Clear
End Select
End Sub

 

[Peter_SSs]

Another layout/approach that you may wish to consider.
Layout as shown and initially (or if you decide to clear out all scores to start again) colour player 1 and remove any colour from other players.
This is Worksheet_Change code so fires when any cells are changed.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range
  Dim TeamTotal As Double
  Dim PlayerScore As Variant
  
  Const TotalRow As Long = 8
  
  Set Changed = Intersect(Target, Range("B:B,E:E"), Rows("3:6"))
  If Not Changed Is Nothing Then
    If Changed.Count = 1 Then
      PlayerScore = Changed.Value
      If IsNumeric(PlayerScore) And PlayerScore <> "" Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        With Changed.EntireColumn
          TeamTotal = PlayerScore + .Cells(TotalRow).Value
          On Error Resume Next
          .SpecialCells(xlConstants, xlNumbers).ClearContents
          On Error GoTo 0
          Changed.Value = PlayerScore
          .Cells(TotalRow).Value = TeamTotal
          .Offset(, -1).Resize(6).Interior.Color = xlNone
          .Cells(IIf(Changed.Row = 6, 3, Changed.Row + 1), 0).Interior.Color = RGB(146, 208, 80)
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
      End If
    End If
  End If
End Sub

Here is my layout and my sheet after a few scores have been entered.

20 03 16.xlsm
	A	B	C	D	E
1	Team 1			Team 2
2	Player	Score		Player	Score
3	Player 1			Player 1
4	Player 2			Player 2
5	Player 3			Player 3	14
6	Player 4	8		Player 4
7
8	Team 1 score	35		Team 2 score	30
Team Scores

 

[breilly00]

Wow,

I was trying this and could get close; but, not as elegant as your code. And yet, I am looking at your code and wondering how to change the logic to high lite the next player. Is what is needed is when one player scores/finishes playing a player on the other team takes there turn.

In this example, the players rotation would be as follows. Player1, player2, player3, player4, player5, player6, player7, player8, player1, player2, etc. etc. Teams play against each other and when a player misses a shot a player from the other team gets to shoot and when they miss a player from the other team shoots.

I think that the change is in the following 2 lines of code. But, changing them is above my pay grade. You have done so much for me; so, I hesitate asking you for another favor. But pretty please with sugar on top how would this code change look?

Offset(, -1).Resize(6).Interior.Color = xlNone

Cells(IIf(Changed.Row = 6, 3, Changed.Row + 1), 0).Interior.Color = RGB(146, 208, 80)

 

[Peter_SSs]

your code.

Always a good idea to make it clear who you are addressing when there has been more than one person responding in the thread. From the code you quoted I assume that it was me you wre addressing.

In this example, the players rotation would be as follows. Player1, player2, player3, player4, player5, player6, player7, player8, player1, player2, etc. etc. Teams play against each other and when a player misses a shot a player from the other team gets to shoot and when they miss a player from the other team shoots.

What does "misses a shot" mean in relation to what is actually entered on the sheet?

Can you step us through an example of some sort?

 

[My Aswer Is This]

Since we have others helping on this and it appears your quoting code that I did not write I will move on and help on another posting.

 

[breilly00]

First things first. peter_SSS must great apologies. I did not look at who was responding. That is my fault. In the future, I will make sure I look.

Now, on to the question.

Missing a shot would mean that we enter a zero for the score.

Once the 2 teams are formed the first player (e.g. Jim) on team number 1 would start shooting the balls into the pocket. Once he misses a shot, the number of points that he makes (e.g. 14 points) would be entered as his score and totaled into the team score. Then the first player(e.g. John) from team 2 would take their turn and continued shooting until he misses a shot. Once they miss his points (e.g. 7) would be entered as his score and added to the team total. Then the second player(e.g. Steve) on Team 1 would take their turn and continuing shooting until he misses (Assume he misses his first shot, then a score of zero would be entered and the total score would not change. Then the second player (e.g. Larry) on team 2 would take their turn and shoot until they missed. Then his score would be added to the team score. This pattern would continue until the fourth player on team 2 shoots. After his turn is over, his points are entered, or zero if he scores no points. Then the first player (back to Jim) on team one takes his turn and the pattern continues.

Once either team scores 200 points the game is over and that team is declared a winner. All points (players and team total are erased and a new game begings.

Please let me know if you have any questions or need any additional information.