Macro/VBA Help. Click cell and value is counted

jamesmev

Board Regular
Joined
Apr 9, 2015
Messages
233
Office Version
  1. 365
Platform
  1. Windows
I am wanting to create a sheet where if I click a cell example (a2) and in this cell a value of 2 is showing. I want to keep a running log on the same sheet of what cells(values) were selected.
after every 15 selections is clears and counts again.

A2 = 2
A3 = 3
a4 = 4

Click on A2 on T4 shows 2
Click on A2 on t5 shows 2
Click a4 on t6 shows 4

etc....

after the count of 15 self clears.

I don't have any idea where to start w/ this one
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
This is sheet code for the sheet of interest. After you install it, manually clear Range T4:T18. Subsequently, whenever you click on a cell in that sheet outside range T4:T18, if the cell is not empty, its value will be added to T4:T18 sequentially until that range is full. T4:T18 will then be cleared and the process starts over.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Ct As Long
Set Target = Target(1)
If Target = "" Then Exit Sub
If Not Intersect(Target, Range("T4:T18")) Is Nothing Then Exit Sub
If Application.CountA(Range("T4:T18")) = 0 Then Ct = 0
Ct = Ct + 1
If Ct > 15 Then
    Range("T4:T18").ClearContents
    Ct = 0
End If
Begin: If Ct > 0 Then
            Range("T4").Offset(Ct - 1, 0).Value = Target.Value
        ElseIf Application.CountA(Range("T4:T18")) = 0 Then
            Ct = 1
            GoTo Begin
        End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top