How to count total clicks in a specified cell in Excel?

This article is talking about counting total clicks in a specified cell in Excel.

Count total clicks in a specified cell with VBA code

Count total clicks in a specified cell with VBA code

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Please do as follows to count total clicks in a specified cell in Excel.

1. In the worksheet contains the cell you need to count its total clicks, right-click the sheet tab, and then click View Code from the context menu.

2. In the Microsoft Visual Basic for Applications window, please copy and paste below VBA code into the Code window.

VBA code: Count total clicks in a specified cell in Excel

Public xRgS, xRgD As Range
Public xNum As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRgS = Range("E2")
    If xRgS Is Nothing Then Exit Sub
    Set xRgD = Range("H2")
    If xRgD Is Nothing Then Exit Sub
    If Intersect(xRgS, Target) Is Nothing Then Exit Sub
    xNum = xNum + 1
    xRgD.Value = xNum
End Sub

Note: In the code, E2 is the cell you need to count its total clicks, and H2 is the output cell of the counting. Please change them as you need.

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

From now on, when clicking on cell E2 in this specified worksheet, the total clicks will be auto-populated in cell H2 as below screenshot shown. For example, if you click on the cell E2 5 times, number 5 will be displayed in cell H2.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

16 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
yoniweis324@gmail.com · 1 years ago
Hi, I'm trying to find a way of counting the number of times 20 different cells are being clicked (each one should be counted separately). I came across your VBA code suggestion, tried to adjust it to my specific needs but it won't work. can you please advise how the code should be written? the cells that I would like to count and the cells that the values should appear in are: F12>AU12, F13>AU13, G12>AV12, G13>AV13, H10>AW10, H11>AW11, H12>AW12, H13>AW13, H14>AW14, H15>AW15, I10>AX10, I11>AX11, I12>AX12, I13>AX13, I14>AX14, I15>AX15, J12>AY12, J13>AY13, K12>AZ12, K13>AZ13).
This is the VBA code I've tried with no success:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRgArray As Variant
Dim xNum
Dim xStrR, xStrS, xStrD As String
Dim xRgS, xRgD As Range

Dim xFNum As Long
xRgArray = Array("F12,AU12", "F13,AU13", "G12,AV12", "G13,AV13", "H10,AW10", "H11,AW11", "H12,AW12", "H13,AW13", "H14,AW14", "H15,AW15", "I10,AX10", "I11,AX11", "I12,AX12", "I13,AX13", "I14,AX14", "I15,AX15", "J12,AY12", "J13,AY13", "K12,AZ12", "K13,AZ13")
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
For xFNum = LBound(xRgArray) To UBound(xRgArray)
xStrR = xRgArray(xFNum)
xStrS = ""
xStrS = Left(xStrR, 2)
xStrD = ""
xStrD = Right(xStrR, 2)
Set xRgS = Nothing
Set xRgS = Range(xStrS)
If TypeName(xRgS) <> "Nothing" Then
Set xRgD = Nothing
Set xRgD = Range(xStrD)
If TypeName(xRgD) <> "Nothing" Then
If TypeName(Intersect(xRgS, Target)) <> "Nothing" Then
xRgD.Value = xRgD.Value + 1
End If
End If
End If
Next
End Sub

Thank you in advance, for your help.
- Reply
To post as a guest, your comment is unpublished.
Rennan Farias · 1 years ago
Como zerar a contagem? How to reset the score?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 11 months ago
  
  Hi,
  If you want to reset the counter, please add the below VBA code at the end of the original code which has been provided above, and then run it.
  
  Sub ClearCount()
  xRgD.Value = ""
  xNum = 0
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
Barbara · 1 years ago
Can you provide a code that allows counting clicks from A2, B2 cells through A14, B14 cells. Thanks in advance.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Barbara,
  Do you mean counting the total clicks in range A2:B14? Or clicks for each cell in range A2:B14?
  
  Reply
To post as a guest, your comment is unpublished.
Andrés · 2 years ago
Hola
Hay alguna manera de programar el conteo de clicks de acuerdo a la fecha, es decir programar varias celdas para que cuenten con la fecha del día?
- Reply
To post as a guest, your comment is unpublished.
Demetrius · 2 years ago
Hello, there is a way to back the counting for any number that I want? For exemple: I'd made 5 clicks, but i just wanted 3. So I change the number in the cell to 3, and when I click again, it continue from 3.
Thank for the code!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  Sorry can’t help you with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  
  Reply
To post as a guest, your comment is unpublished.
Guido · 2 years ago
Thank you for the code, very useful.
I'm not a programmer and I would like to know how to extend this process to every line. That is to say, not only E2>H2 but also E3>H3, E4>H4, and so on.
Is there a code for this?

Thank you in advance!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Guido,
  
  The below VBA code can help you to solve the problem. Please have a try. Thanks for your comment.
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim xRgArray As Variant
  Dim xNum
  Dim xStrR, xStrS, xStrD As String
  Dim xRgS, xRgD As Range
  
  Dim xFNum As Long
  xRgArray = Array("E2,H2", "E3,H3", "E4,H4", "E5,H5", "E6,H6")
  On Error Resume Next
  If Target.Cells.count > 1 Then Exit Sub
  For xFNum = LBound(xRgArray) To UBound(xRgArray)
  xStrR = xRgArray(xFNum)
  xStrS = ""
  xStrS = Left(xStrR, 2)
  xStrD = ""
  xStrD = Right(xStrR, 2)
  Set xRgS = Nothing
  Set xRgS = Range(xStrS)
  If TypeName(xRgS) <> "Nothing" Then
  Set xRgD = Nothing
  Set xRgD = Range(xStrD)
  If TypeName(xRgD) <> "Nothing" Then
  If TypeName(Intersect(xRgS, Target)) <> "Nothing" Then
  xRgD.Value = xRgD.Value + 1
  End If
  End If
  End If
  Next
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Ruth · 2 years ago
  
  Thanks for this. I tried and it worked, however it only worked only until certain number of cells, how can we extend this code until the end of the cells? for example i type in this code below and it only works until "G9,G9". Thanks
  
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim xRgArray As Variant
  Dim xNum
  Dim xStrR, xStrS, xStrD As String
  Dim xRgS, xRgD As Range
  
  Dim xFNum As Long
  xRgArray = Array("C4,C4", "D4,D4", "E4,E4", "F4,F4", "G4,G4", "C6,C6", "D6,D6", "E6,E6", "F6,F6", "G6,G6", "C7,C7", "D7,D7", "E7,E7", "F7,F7", "G7,G7", "C8,C8", "D8,D8", "E8,E8", "F8,F8", "G8,G8", "C9,C9", "D9,D9", "E9,E9", "F9,F9", "G9,G9", "C10,C10", "D10,D10", "E10,E10", "F10,F10", "G10,G10", "C11,C11", "D11,D11", "E11,E11", "F11,F11", "G11,G11", "C14,C14", "D14,D14", "E14,E14", "F14,F14", "G14,G14", "C15,C15", "D15,D15", "E15,E15", "F15,F15", "G15,G15", "C16,C16", "D16,D16", "E16,E16", "F16,F16", "G16,G16", "C17,C17", "D17,D17", "E17,E17", "F17,F17", "G17,G17", "C18,C18", "D18,D18", "E18,E18", "F18,F18", "G18,G18", "C20,C20", "D20,D20", "E20,E20", "F20,F20", "G20,G20")
  On Error Resume Next
  If Target.Cells.count > 1 Then Exit Sub
  For xFNum = LBound(xRgArray) To UBound(xRgArray)
  xStrR = xRgArray(xFNum)
  xStrS = ""
  xStrS = Left(xStrR, 2)
  xStrD = ""
  xStrD = Right(xStrR, 2)
  Set xRgS = Nothing
  Set xRgS = Range(xStrS)
  If TypeName(xRgS) <> "Nothing" Then
  Set xRgD = Nothing
  Set xRgD = Range(xStrD)
  If TypeName(xRgD) <> "Nothing" Then
  If TypeName(Intersect(xRgS, Target)) <> "Nothing" Then
  xRgD.Value = xRgD.Value + 1
  End If
  End If
  End If
  Next
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 2 years ago
  
  Hi Ruth,
  The code is hard to optimized for meeting your needs. Sorry about that.
  
  Reply
To post as a guest, your comment is unpublished.
Dennis · 2 years ago
How can you "reset" the counter?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Dear Dennis,
  Please add the below VBA code at the end of the original code. Every time you run this code, the counting will be reset to 0. Thank you for your comment.
  
  Sub ClearCount()
  xRgD.Value = ""
  xNum = 0
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  hiddenxyz@yahoo.com · 11 months ago
  
  Crystal,
  
  Can you provide the full VBA code - for this? also how would I apply it to a single row - each needing its own counter?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 11 months ago
  
  Hi,
  The full VBA code is as follows. If you want to reset the counter, please run the second VBA code. For applying the code to a single row, sorry can't help you yet.
  
  'The first VBA
  Public xRgS, xRgD As Range
  Public xNum As Long
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRgS = Range("E2")
  If xRgS Is Nothing Then Exit Sub
  Set xRgD = Range("H2")
  If xRgD Is Nothing Then Exit Sub
  If Intersect(xRgS, Target) Is Nothing Then Exit Sub
  xNum = xNum + 1
  xRgD.Value = xNum
  End Sub
  'The second VBA
  Sub ClearCount()
  xRgD.Value = ""
  xNum = 0
  End Sub
  
  Reply

Login to your account

Password Reset