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

 

[bebo021999]

I came up as following:
There are 3 buttons: one for player1, one for player2 and one for Undo
Player1 & 2's name can be changed, and the two buttons changed accordingly.
Below code is placed in worksheet module (not general module)

game.xlsm

drive.google.com

Option Explicit

Private Sub CommandButton1_Click()
collect
Range("A6").Value = Me.CommandButton1.Caption
Range("B6").Value = Range("B7").Value + 1
Range("C6").Value = Range("C7").Value + 0
End Sub

Private Sub CommandButton2_Click()
collect
Range("A6").Value = Me.CommandButton2.Caption
Range("B6").Value = Range("B7").Value + 0
Range("C6").Value = Range("C7").Value + 1
End Sub

Private Sub CommandButton3_Click()
Range("A6:C6").Delete shift:=xlUp
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:C5")) Is Nothing Then Exit Sub
Me.CommandButton1.Caption = Range("B5").Text
Me.CommandButton2.Caption = Range("C5").Text
End Sub

Sub collect()
Dim lr&, i&, k&, rng, arr(1 To 1000, 1 To 3)
lr = WorksheetFunction.Max(6, Cells(Rows.Count, "B").End(xlUp).Row)
rng = Range("A6:C" & lr).Value
k = 1
For i = 1 To UBound(rng)
    k = k + 1
    arr(k, 1) = rng(i, 1): arr(k, 2) = rng(i, 2): arr(k, 3) = rng(i, 3)
Next
Range("A6").Resize(k, 3).Value = arr
End Sub

 

[JenniferMurphy]

I came up as following:
There are 3 buttons: one for player1, one for player2 and one for Undo
Player1 & 2's name can be changed, and the two buttons changed accordingly.
Below code is placed in worksheet module (not general module)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:C5")) Is Nothing Then Exit Sub
Me.CommandButton1.Caption = Range("B5").Text
Me.CommandButton2.Caption = Range("C5").Text
End Sub

Wow! I didn't expect to be given actual code. Thank you.

I am working my way through it. I don't understand the parameters in the Wordsheet_Change code:

Private Sub Worksheet_Change(ByVal Target As Range)

What is "target"?

Thanks

 

[JenniferMurphy]

Wow! I didn't expect to be given actual code. Thank you.

I am working my way through it. I don't understand the parameters in the Wordsheet_Change code:

Private Sub Worksheet_Change(ByVal Target As Range)

What is "target"?

Thanks

I think I figured that one out. Target is the part of the worksheet (cell?) that changed, right?

So this will be invoked whenever any changes are make to any cells, right?

And the Intersect instruction tells the Sub whether the change happened in the cell it is checking, right?

One more question: How do I find the "name" of the command button? That is, what do I put betqwwewen "me." and ".caption" in this code:

Me.CommandButton1.Caption = Range("B5").Text

 

[JenniferMurphy]

I think I figured that one out. Target is the part of the worksheet (cell?) that changed, right?

So this will be invoked whenever any changes are make to any cells, right?

And the Intersect instruction tells the Sub whether the change happened in the cell it is checking, right?

One more question: How do I find the "name" of the command button? That is, what do I put betqwwewen "me." and ".caption" in this code:

Me.CommandButton1.Caption = Range("B5").Text

OK, I found the name of the buttons. They are in the Name Box! Duh!

I downloaded your workbook. It works perfect;y. Thanks for that. But the burron controls appear to be locked. I want to see what's inside of them. How can I unlock them so I can select and view them?

 

[bebo021999]

All your guesses are correct!
And nice to hear it works.
To come inside button, try to activate the "Design Mode" in Developer tab.

 

[JenniferMurphy]

Your code works, but mine does not. I can't figure out why. The problem seems to be in the name of the button control.

Here's a screenshot showing one of the buttons and its name as "Button 2" displayed in the Name Box.

Here's a screen shot of the code that seems identical to yours, but does not work.

If I change anything in Sheet2, this Sub gets executed, but gets this error on the Me. statement.

I tried all of these variations. None of them work.

Me.CommandButton2.Caption = Range("C4").Text
Me.CommandButton_2.Caption = Range("C4").Text
Me.Button_2.Caption = Range("C4").Text
Me.Button2.Caption = Range("C4").Text

Any help as to what I am doing wrong?

Thanks

 

[bebo021999]

How did you insert the buttons?
Make sure its Active-X controls (Develope tab/insert/Active X Controls then hit commandbutton)

 

[JenniferMurphy]

All your guesses are correct!
And nice to hear it works.
To come inside button, try to activate the "Design Mode" in Developer tab.

OK, that worked. Thanks

 

[JenniferMurphy]

How did you insert the buttons?
Make sure its Active-X controls (Develope tab/insert/Active X Controls then hit commandbutton)

Aha! That's the problem. I inserted a Form control. I had always heard that Form controls and safer than Active-X controls.

Can I make this work with Form controls? Or do I have to switch to Active-X controls?