Counting in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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??
 
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
 
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
 
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.
 
Maybe you haven't seen my last post yet, I can send a workbook to your
email, if desired.
 
Back
Top