Best implementation of scoring sheet for grandkids

[JenniferMurphy]

The grandkids love games, especially computer games. When they were little, we played tic-tac-toe. Now we are into more difficult games, like Connect 4. I recently played against my 9 year old, Susie. She beat me several games in a row, so she started keeping score. That gave me an idea to create a scoring sheet that we could use to keep a running score. I might add a graph. In addition to keeping score, it might pique their interest in spreadsheets in particular and math in general. It might give me a chance to show them how it works.

My initial thought was a table something like this with a control button that would add a row and increment the corresponding score.

But Susie has siblings who will almost certainly want to play and she may want to play against mom or dad. So now I am thinking of a more general design that can be easily adapted to any two players, something like this.

I would assign the name Player1 to the cell currently containing "Susie" and Player2 to the one containing "Gramma". The Score It button would ask who won and score it accordingly. If all names are local to the sheet, I should be able to easily make a copy, change the names, and everyhting should continue to work.

A couple of questions:

1. Does anyone see a problem with this plan?
2. Is there a better way?
3. Is there a way I can have a MsgBox-like control, but with buttons that have the names of the two players, instead of Yes and No? How about if I have the players select the name of the winner and then click the Score It button. The code can check the the selected cell for the name?

I would appreciate any suggetions.

Thanks

 

[JenniferMurphy]

I discovered that the Undo code only shifts the specific columns down, not the entire row. The Score buttons code shifts the entire row. I modified them to only shift the columns. It seems to work. Here's the code for the Player 1 button. It's kinda klunky. Is there a more elegant way?

Sub Player1Button_Click()

'Add the winner's name
Range(rnWinnerHdr).Offset(1, 0).Select
With Selection
  .Insert shift:=xlDown
End With
Range(rnWinnerHdr).Offset(1, 0).Select
With Selection
  .Value = Me.Shapes("Player1 Button").OLEFormat.Object.Caption
  .HorizontalAlignment = xlLeft
  .Font.Bold = False
End With

'Add 1 to player1's score
Range(rnPlayer1Hdr).Offset(1, 0).Select
With Selection
  .Insert shift:=xlDown
End With
Range(rnPlayer1Hdr).Offset(1, 0).Select
With Selection
  .Value = Range(rnPlayer1Hdr).Offset(2, 0) + 1
  .HorizontalAlignment = xlCenter
  .Font.Bold = False
End With

'Keep player2's score the same
Range(rnPlayer2Hdr).Offset(1, 0).Select
With Selection
  .Insert shift:=xlDown
End With
Range(rnPlayer2Hdr).Offset(1, 0).Select
With Selection
  .Value = Range(rnPlayer2Hdr).Offset(2, 0)
  .HorizontalAlignment = xlCenter
  .Font.Bold = False
End With

End Sub

 

[JenniferMurphy]

I discovered a ittle problem. If I make a copy of the sheet so that I can track two different players, I have to manually update the macro assignments for the three buttons.

Is there a way that I can move the code to a code module so that it will work on any sheet in the workbook? I hate having multiple copies of identical code.