tally by clicking cell

[Lou]

I am working on a tally sheet. I would like the cell to
add by 1 every time the cell is clicked on by the mouse.

I think this is probably going to be an easy answer...but
I stumped.

Please help.

Thanks

 

[JMay]

Maybe not pretty or efficient, but it works for me (sample - change cell
references accordingly) - you must Double-click on cell, not single-click.
HTH

 

[JMay]

daaa...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim MyCumNum As Integer
MyCumNum = Range("B5")
Target.Value = MyCumNum + 1
Range("A1").Select
End Sub

 

[Dave Peterson]

The bad news is excel doesn't have anything that monitors clicking on a cell.

You could add a shape over the cell and catch that or you could double click on
the cell and do the same thing.

Here's two routines. If you doubleclick on the cell, it adds 1. If you
rightclick it subtracts one.

Right click on the worksheet tab that should have this behavior and select view
code. Paste both of these in:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = Target.Value + 1
Beep
Cancel = True
End If

errHandler:
Application.EnableEvents = True

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = Target.Value + -1
Beep
Cancel = True
End If

errHandler:
Application.EnableEvents = True
End Sub

I used A1 as my accumulator cell.


One bad thing about this kind of event macro is that it kills the Undo list. If
that's important to keep, don't use this.