Auto Sum in the Same Cell

[CarlCarlos]

Dear all,

I have had an odd request, and for the life of me cannot think of a way to do it or if it is even possible:

My colleague wishes to type a '1' or a 'Y' into cell B1, this 1 or Y will represent a good response to a questionaire. She wishes to put a 1 into this cell for every good response and wants it to sum how many times she puts a 1 into it.

Now i know it is not hard to put a 1 in and then next response put a 2,3 etc etc.

But i was wondering if it was possible for this to be done automatically

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B1" Then
    Application.EnableEvents = False
    Application.Undo
    Target.Value = Target.Value + 1
    Application.EnableEvents = True
End If
End Sub

 

[CarlCarlos]

Thank You, this is very useful.

She now asks if she makes a mistake, how can she remove a 1?

 

[VoG]

Try this: press Delete to decrease by 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B1" Then
    Application.EnableEvents = False
    If Target.Value = "" Then
        Application.Undo
        Target.Value = Target.Value - 1
    Else
        Application.Undo
        Target.Value = Target.Value + 1
    End If
    Application.EnableEvents = True
End If
End Sub