Best implementation of scoring sheet for grandkids

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,196
Office Version
  1. 365
Platform
  1. Windows
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.
1664930926912.png

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.
1664931137413.png

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

Well-known Member
Joined
Jul 23, 2011
Messages
2,196
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,196
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
1,186,769
Messages
5,959,650
Members
438,440
Latest member
Hi577

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top