Counting in a cell

P

Philip Drury

A colleague is reporting on a recent survey, she has set up the questions in
Column A and the potential answers across row 1. What she wants is that each
time she clicks the mouse in the relevant cell it will count from 1 upwards -
does anyone know if this is possible???
Thanks
 
D

Don Guillett

right click sheet tab>view code>copy/paste ALL of this.
Now when cell a5 or cell b5 is selected the cell count will increment by one
'==========
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
'============
A variation for a double click instead of select
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As
Boolean)
If target.Address = "$A$5" Or target.Address = "$A$6" Then
On Error GoTo fixit
Application.EnableEvents = False
If target.Value = 0 Then oldvalue = 0
target.Value = 1 * target.Value + 1
oldvalue = target.Value
fixit:
Application.EnableEvents = True
End If
Cancel = True
End Sub
 
P

Philip Drury

Don, thanks for that, saved me once again! How can I change this so that the
cells are not linked i.e. a click in A5 wil increment that cell by one and a
click in cell B5 will do likewise but not linked to A5 - does that make
sense??
 
P

Philip Drury

If I click in A5 it counts to 1, clicking in B5 then gives a count of 1 in
that cell - then a click in A5 gives a count of 2 in that cell, howvever a
click in B5 gives a count of 3 as it increments the total of A5 by 1
 
D

Don Guillett

Change to add "oldvalue = target" as below

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

oldvalue = target

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
 
P

Philip Drury

Did what you said, again, and the cells still do not increment independantly
of eachother, the selected cell will always increment by one based on the
value of the other cell.
 
D

Don Guillett

Maybe you haven't seen my last post yet, I can send a workbook to your
email, if desired.
 
Top