Counting Click on Cell via VBA

  • Hi,


    I am trying to write a code such that each time the cell in a specific row is click, it would change to a color and the counter which track the total number of cell in the row has been selected will count.

    However, I can only achieve the change color but the counter would not continue to increase after it change to one.

    Can someone advice me on what is wrong with my coding?


    Private Sub Worksheet_DoubleClick(ByVal Target As Range, Cancel as Boolean)


    If Not Intersect (Target, Me.Range("C17:I17)) Is Nothing Then

    Target.Interior.ColorIndex = 4

    Cancel = True


    If IsNumeric(Me.Range("C17:I17").Value) Then

    Range("G4").Value = Range("G4").Value + 1


    Else

    Range("G4").Value = 1


    End If

    End If


    End sub

  • Hello and Welcome to the Forum :)


    Your final objective is not extremely clear to me ...


    But regarding your macro, you could test following


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Intersect(Target, Range("C17:I17")) Is Nothing Then Exit Sub
    3. Target.Interior.ColorIndex = 4
    4. If IsNumeric(Target) Then
    5. Range("G4").Value = Range("G4").Value + 1
    6. Else
    7. Range("G4").Value = 1
    8. End If
    9. Cancel = True
    10. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited 2 times, last by Carim ().

  • Hello,


    Sorry ... but your macro says the counter should only be increased when the Target cell is NUMERIC ...


    and in your sample ....all your cells are TEXT ... !!!


    May be what you need is the following ...

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • You are welcome ;)


    If you need to test Text within VBA ... you can use : Application.WorksheetFunction.IsText


    Glad to hear you have solved your problem

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    Currently I have wrote the code to change cell color when click and increase counter by 1 for 1st row of data.

    However, I cant do it for the 2nd row of data to achieve the same effect.

    Can someone advice me on what is wrong with my coding?


    Additionally, can advice on how to write the code such that the clicked cell that had turned green can never trigger the counter to increase counter by second time click.

  • rory

    Changed the title of the thread from “Help needed on Counting Click on Cell via VBA coding” to “Counting Click on Cell via VBA”.
  • Hello,


    You are awesome!!

    Thanks alot..appreciate it.. :)

    Thank you!


    Just wondering.. am I able to set the condition of the cell such that once it is clicked and changed color, it would not be able to be clicked again?

    This is so that the counter would only count once.


    I tried to insert a few code but then there would be some errors..

  • Hello,


    Thanks for your Thanks AND for the Like :)


    Attached is your Version 3 to avoid double counting ...;)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks for your Like :)


    Assume the proposed solution fits the bill ...


    If that is the case, thanks for marking the Thread As Solved ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().

  • Hello,


    All looks ok but I am facing difficulties trying to trial and error some codes to lock some cell (i.e. highlighted in black) such that user will not be able to click and trying to set counter to start always at 0.


    Any idea?

  • Hello,


    Do not know the process you need to have to Start your tracking ... so have added a command button ...


    Hope attached Version 4 is getting closer to your objectives ;)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Glad to hear you are progressing ;)


    Thanks a lot for your Thanks ...AND for the Like :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,


    Can I check is there word limit to the number of words in each cell?

    I realized that when I click on some cells where I typed in longer words text, the cell would either not cause the counter to add 1 or it would rest the values in the counter to 1.

    Can advice?

  • I am having difficulty trying to debug cell F46.


    F46 will add counter by 1 if it is the first cell to be clicked.

    Otherwise, it will not add 1 to the counter when if it is the second or third cell to be click.


    Sometimes, F46 changes the existing values in the counter E37 to 1 when click.

    Can advice?