How to write a macro to count clicks in Excel?

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

Guest

Hello....I would like to write a macro/formula in Excel such that each click
in a cell will add 1 to another cell, ie the number of clicks will be
counted. It can be either a left or right click....Is this possible??
Thanks!!

Steve
(e-mail address removed)
 
Rightclick on the worksheet tab that should have this behavior and select view
code.

Paste this into the code window:

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Cancel = True

With Target.Offset(0, 1)
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
Beep
End If
End With
End Sub

If you rightclick on A1, it'll add 1 to B1.

(You could use Worksheet_BeforeDoubleClick, too. But there isn't a single click
event.)
 
Dave Peterson said:
Rightclick on the worksheet tab that should have this behavior and select view
code.

Paste this into the code window:

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Cancel = True

With Target.Offset(0, 1)
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
Beep
End If
End With
End Sub

If you rightclick on A1, it'll add 1 to B1.

(You could use Worksheet_BeforeDoubleClick, too. But there isn't a single click
event.)



GoBoilerzz wrote:
>
> Hello....I would like to write a macro/formula in Excel such that each click
> in a cell will add 1 to another cell, ie the number of clicks will be
> counted. It can be either a left or right click....Is this possible??
> Thanks!!
>
> Steve
> (e-mail address removed)

--

Dave Peterson
(e-mail address removed)
Click to expand...



hi,
i found a macro for left click, but problem is "Clicking cell (B4) became emty after clicking.

I want to know is there any way to retain the cell contant even after clicking. another question is how to apply this macro for one or more column. sorry I am don't now VBA. Sinciarly I copy the code from website.

see the code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B4")) Is Nothing Then
If IsNumeric(Range("B4").Value) Then
Range("A4").Value = Range("A4").Value + 1
Else
Range("A4").Value = 1
End If
End If

End Sub

----------

see the attached excel file and goto sheet 1
 

Attachments

I want to do exactly what Steve wanted and Dave Peterson's code did. Right click and update a different cell by 1. But I also want to do it for other cells within the sheet. I adjusted the code so that it does it for all cells from B4:b22 but how do I change the code so that I can add additional ranges of cells? ie I also want to have d4:d22 to update the cells 11 columns on. And F4:f22 etc etc. ie I need it to check several ranges of cells and then perform the update count accordingly. Can it all be done in one script?

Changes shown below

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b4:b22")) Is Nothing Then Exit Sub
Cancel = True

With Target.Offset(0, 11)
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
Beep
End If
End With
End Sub
 
Back
Top