| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting in a cell
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting in a cell
right click sheet tabview codecopy/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 Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting in a cell
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?? "Don Guillett" wrote: right click sheet tabview codecopy/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 Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting in a cell
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 "Don Guillett" wrote: That is what should happen as written. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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?? "Don Guillett" wrote: right click sheet tabview codecopy/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 Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting in a cell
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 "Don Guillett" wrote: That is what should happen as written. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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?? "Don Guillett" wrote: right click sheet tabview codecopy/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 Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting in a cell
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. "Don Guillett" wrote: right click sheet tabview codecopy/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 Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting in a cell
Maybe you haven't seen my last post yet, I can send a workbook to your
email, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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. "Don Guillett" wrote: right click sheet tabview codecopy/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 Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting in a cell
That would be really helpful, thanks
"Don Guillett" wrote: Maybe you haven't seen my last post yet, I can send a workbook to your email, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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. "Don Guillett" wrote: right click sheet tabview codecopy/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 Guillett Microsoft MVP Excel SalesAid Software "Philip Drury" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Avoiding counting the same cell twice | Excel Worksheet Functions | |||
| Counting within a cell | Excel Worksheet Functions | |||
| Counting keystrokes within a cell | Excel Worksheet Functions | |||
| Counting Entries in a Cell | Excel Worksheet Functions | |||
| Counting In a Cell | Excel Worksheet Functions | |||
Linear Mode
