Click on cell to increment contents by 1

[Guest]

I want to create a tally sheet in Excel, so that when you click on the cell
its contents are increased by 1. It would be great to be able to right click
and have the contents go down by one! Anybody know a solution?

Thanks

Excel 2003

 

[Dave Peterson]

Not a single mouse click, but you could count a double click or rightclick.

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop editing in cell
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop pop up from showing
If IsNumeric(Target.Value) Then
Target.Value = Target.Value - 1
End If
End Sub

I used any cell in Column A. You can change that in both spots if you want.
Doublclicking will add 1. Rightclicking will subtract 1.

 

[Anita]

This is really useful for me. I've changed the formula from column A to a row
within the worksheet but is it possible to have multiple rows in one
worksheet with this function?
Thanks

 

[Dave Peterson]

Yep.

Change this line to what you need:
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Maybe
If Intersect(Target, Me.Range("1:2")) Is Nothing Then Exit Sub
or
If Intersect(Target, Me.Range("1:1,3:3")) Is Nothing Then Exit Sub