Help my macro store a value in a cell

[JenniferMurphy]

I want to create a little score sheet for Connect 4 games with my granddaughter. Here's the table:

Connect 4 Scores.xlsm
	B	C	D
4	Winner	Susie	Gramma
5	Gramma	2	1
6	Susie	2	0
7	Susie	1	0
8		0	0
Sheet1

As each game is played, we will click on one of two button form controls: Susie or Gramma. The macro called will add a new row at the top, enter the name of the winner in the first column, and increment the score for that person, keeping the score for the other one constant.

The mini-sheet does not show the named ranges, so here's a screen shot of the Name Manager:

And here's the macro code I have so far:

Option Explicit

'     Global Constants

' Set the range names that we will need
Public Const rnWinnerHdr As String = "WinnerHdr"
Public Const rnSusieHdr  As String = "SusieHdr"
Public Const rnGrammaHdr As String = "GrammaHdr"

'==============================================================
' Score a win for Susie
'==============================================================
Sub ScoreSusie()

Dim rWinnerHdr As Range
Set rWinnerHdr = Range(rnWinnerHdr)
Dim Row1 As Long
Row1 = rWinnerHdr.Row + 1

Rows(Row1).Select
Selection.Insert Shift:=xlDown
Range(rWinnerHdr).Offset(1, 0).Value = "Susie"

End Sub

This all works except for the last statement where I am trying to save the text string "Susie" in the Winner column.

Can someone tell me what I am doing wrong?

Thanks

 

[RoryA]

rWinnerHdr is a range object, not the name of a range, so it would just be:

rWinnerHdr.Offset(1, 0).Value = "Susie"

 

[bebo021999]

With C4 & D4 is player name, I would suggest using Worksheet_SelectionChange even on these cells.
Right click on sheet name, View Code, then paste below code into:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tg As Range
If Intersect(Target, Range("C4:D4")) Is Nothing Or Target.Count > 1 Then Exit Sub
Set tg = Target
If MsgBox("Score for " & Target.Value & "?", vbYesNo) = vbNo Then GoTo z
Range("B4:D4").Offset(1, 0).Insert shift:=xlDown
Select Case tg.Column
    Case 3
        tg.Offset(1, -1).Value = tg.Value
        tg.Offset(1, 0).Value = tg.Offset(2, 0).Value + 1
        tg.Offset(1, 1).Value = tg.Offset(2, 1).Value + 0
    Case Else
        tg.Offset(1, -2).Value = tg.Value
        tg.Offset(1, -1).Value = tg.Offset(2, -1).Value + 0
        tg.Offset(1, 0).Value = tg.Offset(2, 0).Value + 1
End Select
z:
Range("B4").Select
End Sub

To islutrate what happen before and after code execution, see image attached:
Click on "Susie"

If "No", cancel.
If "Yes" :

 

[JenniferMurphy]

rWinnerHdr is a range object, not the name of a range, so it would just be:

rWinnerHdr.Offset(1, 0).Value = "Susie"

Thanks. That works.

So, am I going to extra unnecessary work by creating the range object? This code also works.

Range(rnWinnerHdr).Offset(1, 0).Value = "Susie"

Are there any advantages to having the range object vs just using the range name?

 

[JenniferMurphy]

With C4 & D4 is player name, I would suggest using Worksheet_SelectionChange even on these cells.

I am afraid that I would need some time to understand this code.

Are you saying that this code will allow me to score a win for either person just by clicking on their name? How is this preferable to my having two button controls, one for Susie and one for Gramma? I'm also going to have an Undo button.

 

[JenniferMurphy]

With C4 & D4 is player name, I would suggest using Worksheet_SelectionChange even on these cells.

After studying your suggestion, I think it might have given me an idea for a better implementation. I'll start that in another thread.