| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting clicks
I want to count the clicks in any of a large range of cells in one column.
This is for purposes of collecting clicks like a person making hash marks on an inventory count sheet. There is some brilliant code on another forum but it only works on a couple or a few cells, as named within the code. I cannot make it work throughout a column. This is the code: 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting clicks
Hi,
Try this. the fixit subroutine has probably been added for debugging and does nothing Option Explicit Dim oldvalue As Double Private Sub Worksheet_SelectionChange(ByVal target As Range) If target.Column = (1) And target.Cells.Count = 1 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 Mike "rengewwj" wrote: I want to count the clicks in any of a large range of cells in one column. This is for purposes of collecting clicks like a person making hash marks on an inventory count sheet. There is some brilliant code on another forum but it only works on a couple or a few cells, as named within the code. I cannot make it work throughout a column. This is the code: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting clicks
Change your "IF" statement to something like:
If Not Intersect(Target, Columns("B:B")) Is Nothing Then Change the column to suit. HTH Otto "rengewwj" wrote in message ... I want to count the clicks in any of a large range of cells in one column. This is for purposes of collecting clicks like a person making hash marks on an inventory count sheet. There is some brilliant code on another forum but it only works on a couple or a few cells, as named within the code. I cannot make it work throughout a column. This is the code: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting clicks
Perhaps:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Then Application.EnableEvents = False Target.Value = Target.Value + 1 Application.EnableEvents = True End If End Sub -- Gary''s Student - gsnu200822 "rengewwj" wrote: I want to count the clicks in any of a large range of cells in one column. This is for purposes of collecting clicks like a person making hash marks on an inventory count sheet. There is some brilliant code on another forum but it only works on a couple or a few cells, as named within the code. I cannot make it work throughout a column. This is the code: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting clicks
Works like a charm. Thank you.
"Mike H" wrote: Hi, Try this. the fixit subroutine has probably been added for debugging and does nothing Option Explicit Dim oldvalue As Double Private Sub Worksheet_SelectionChange(ByVal target As Range) If target.Column = (1) And target.Cells.Count = 1 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 Mike "rengewwj" wrote: I want to count the clicks in any of a large range of cells in one column. This is for purposes of collecting clicks like a person making hash marks on an inventory count sheet. There is some brilliant code on another forum but it only works on a couple or a few cells, as named within the code. I cannot make it work throughout a column. This is the code: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting clicks
Small typo here. For first statement use
If Not Intersect(Target, Range("A:A")) is Nothing Then best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Gary''s Student" wrote in message ... Perhaps: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Then Application.EnableEvents = False Target.Value = Target.Value + 1 Application.EnableEvents = True End If End Sub -- Gary''s Student - gsnu200822 "rengewwj" wrote: I want to count the clicks in any of a large range of cells in one column. This is for purposes of collecting clicks like a person making hash marks on an inventory count sheet. There is some brilliant code on another forum but it only works on a couple or a few cells, as named within the code. I cannot make it work throughout a column. This is the code: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Repetitive keystrokes and/or mouse clicks | Excel Discussion (Misc queries) | |||
| Double clicks do not open the file | Excel Discussion (Misc queries) | |||
| How to have errr msg pop up when user clicks on locked cell | Excel Discussion (Misc queries) | |||
| auto hiding rows under category heading unless cursor clicks on to | Excel Discussion (Misc queries) | |||
| unable to open Excel file by double clicks | Excel Discussion (Misc queries) | |||
![rengewwj[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
