add 1 when click

  • Thread starter Thread starter Dylan
  • Start date Start date
D

Dylan

Hi,
does anyone know how to program a cell when every single
click on that cell with mouse button, the value of the
cell will add 1. for example: Cell A1 had a value of 7,
when i click on cell A1, the value will go up to 8 and so
on.
 
Dylan,

Right-click the Sheet Tab, View Code, Insert this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Target.Value = Target.Value + 1
End Sub

If the Column is A then add 1


It will activate every time you select the cell. Unlike clicking a cell it
only triggers when you change the selection from a different cell.
 
Dylan,

Right-click the Sheet Tab, View Code, Insert this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Target.Value = Target.Value + 1
End Sub

If the Column is A then it adds 1

It will activate every time you select the cell. Unlike clicking a cell it
only triggers when you change the selection from a different cell.

Rob
 
-----Original Message-----
Dylan,

Right-click the Sheet Tab, View Code, Insert this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Target.Value = Target.Value + 1
End Sub

If the Column is A then it adds 1

It will activate every time you select the cell. Unlike clicking a cell it
only triggers when you change the selection from a different cell.

Rob





.
Click to expand...

hi Rob,

thanks for help. it works. what i see is that the whole
column will be affected. could you teach me how to amend
the script for only a particular Cell instead? also could
i do it such that the value change On_click instead of
clicking elsewhere then back on the cell. thanks very much.
 
Hi Dylan
One way: Change the code to the following (now only A1 is counted):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
Else
Target.Value = Target.Value + 1
end if
End Sub
 
Hi Dylan,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If not intersect (Target, Range("A1")) Is Nothing Then
Target.Value = Target.Value + 1
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Dylan,

I don't think there is a trivial way to capture onclick for a worksheet.
Because SelectionChange fires even when you use the cursor keys to enter
that cell, I suggest another approach:

Create a button and assign this macro to it, which in this example
increments selection if it falls within the range A1:B20
If selection is outside of bounds, then it beeps as an error.

Sub Increment_Click()
Dim rng As Range, rngSel As Range

Set rngSel = Intersect(Selection, Range("A1:B20"))
If Not rngSel Is Nothing Then
For Each rng In rngSel
rng.Value = rng.Value + 1
Next
Else
Beep
End If
End Sub


Rob
 
Back
Top