creating a house points scoring program

One way is to use the Sheet Change event procedure.
With the cell in which you enter the points named "points" and the cumulative points cell named "cumpoints" then

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Points")) Is Nothing Then
        Range("CumPoints") = Range("Cumpoints") + Target
        Target = ""
    End If
End Sub

Hi,
First create the two named cells I mentioned. Then:


1. Right click the sheet tab name and select 'View Code
2. On the right in the left of the two drop down boxes select 'Worksheet'
3. In the right hand drop down select the 'Change' option
4, Add the code I gave you inside this code window.
5.

At the risk of being told I am hijacking this thread (if anyone feels that strongly, I will start a new thread, referencing this 1)...

Richard, I was trying to do this with msgbox's and am getting in a big mess. My concept was as follow...
- start with 3 cells...1 for each house points
- msgbox to ask for how many points to add
- msgbox to ask which house gets them (Im sure this could be done with buttons, but Im taking baby steps)
- based on which house is selected (1, 2, or 3), the points get added to what was already there

I can get the points to add
I can get the house
I cannot get the points to go to the cell (probably because Im not telling it where to go?)
Or, if I do get them to go where they need to, they replace what was there

Sub Add_to_total()
    Dim HouseInput As Long, House1 As Long, House2 As Long, House3 As Long, Points As Long
'    displ = MsgBox("add new house points?", vbYesNo)
'    If displ <> vbYes Then Exit Sub
    Points = InputBox("Enter Points")
    HouseNumber = InputBox("Enter House Number (1, 2 or 3)")
    MsgBox Points
    MsgBox House1
    If HouseNumber = 1 Then House1 = House1 + Points
    MsgBox House1
    Range("a2").Select = House1
    If HouseNumber = 2 Then House2 = House2 + Points
    Range("B2").value = House2
    If HouseNumber = 3 Then House3 = House3 + Points
    Range("C2") = House3
End Sub

Remember, baby steps, I am not even up to learner level yet lol

Hi Ford

The main problem is that you'd Dimmed the variables as Local variables, i.e. the values only persist until the procedure's End Sub. You could either

1. Declare the variables as Public variables above the Procedure. In which case they will persist when the procedure has finished and will still contain a value the next time the procedure is run.

OR

2. Use IF...Then in a block not as a single line. e.g.

If housenumber = 1 Then 
House1 = House1 + Points
End If

This means the If tests for the two house numbers that are not in play don't ever get actioned. With the single line IF...Then construct then the succeeding line for all three get actioned, i.e.

Range("a2") = House1

Another way is with the Select Case statement which I generally prefer, particularly if there are a lot of IF statements. i.e.

Sub Add_To_Total()
Dim Points As Long, housenumber As Long
    Points = InputBox("Enter Points")
    housenumber = InputBox("Enter House Number (1, 2 or 3)")
    Select Case housenumber
        Case Is = 1
        Range("A2") = Range("A2") + Points
        Case Is = 2
        Range("B2") = Range("B2") + Points
        Case Is = 3
        Range("C2") = Range("C2") + Points

    End Select
End Sub

or a slight mod to the Select Case idea

Sub AddTotal()
Dim Points As Long, housenumber As Long, r As Range
    Points = InputBox("Enter Points")
    housenumber = InputBox("Enter House Number (1, 2 or 3)")
    Select Case housenumber
        Case Is = 1
        Set r = Range("A2")
        Case Is = 2
        Set r = Range("B2")
        Case Is = 3
        Set r = Range("C2")
    End Select
    
    r = r + Points


End Sub

Thats for that, Richard. In the interests of KISS, I will stick with your 1st Select Case option. I knew there was a way to do that