add 1 to a sum by clicking mouse?

M

muzic247

In Excel, can 1 be added to the sum by clicking once in a cell? For
instacne, if I click once in a cell correspong to the cell that displays the
sum of a row, one will be added. Each time I click the mouse, 1 is added to
the total? Just like a small hand held clicker.
 
D

Don Guillett

right click sheet tab>view code>insert this>save. Now when you just select
cell a5 1 will be added to the sum.


Option Explicit
Dim oldvalue As Double
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$5" Or Target.Address = "$B$5" Then
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 + oldvalue
oldvalue = Target.Value
Application.EnableEvents = True
End If
End Sub
Sub fixit()
Application.EnableEvents = True
End Sub
 
D

Dave Peterson

Nope. There's nothing that fires when you click on a cell.

But maybe you could tie into the _beforedoubleclick and _beforerightclick
events:

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.
 
G

Gord Dibben

How about adding a Spinner from the Forms Toolbar

You can click to add or click to reduce.


Gord Dibben MS Excel MVP
 
Top