Help my macro store a value in a cell

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,491
Office Version
  1. 365
Platform
  1. Windows
I want to create a little score sheet for Connect 4 games with my granddaughter. Here's the table:
Connect 4 Scores.xlsm
BCD
4WinnerSusieGramma
5Gramma21
6Susie20
7Susie10
800
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:
1664868807393.png

And here's the macro code I have so far:
VBA Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
rWinnerHdr is a range object, not the name of a range, so it would just be:

Code:
rWinnerHdr.Offset(1, 0).Value = "Susie"
 
Upvote 0
Solution
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:
VBA Code:
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"

Capture1.JPG

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

Capture2.JPG
 
Upvote 0
rWinnerHdr is a range object, not the name of a range, so it would just be:

Code:
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.

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

Are there any advantages to having the range object vs just using the range name?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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
Back
Top