How to count the number of times a form button has been clicked.

[EzzEldean]

Hello everyone,

Hope everyone doing alright. I am seeking your help to do something with my sheet. As the title implies, I want to count how many times a form button was clicked that has a macro assigned to it.
I want also to set conditional formatting or a message box to appear that if the number of counting reaches 3 times either to highlight the cell that contains the number 3 or a message box that this form button has been clicked 3 times, Knowing that I have about 300 form buttons for each ID.

 

[EzzEldean]

@EzzEldean Without a clear definition of what you have and what you want, neither I nor @Flashbond can be confident in providing you with or, pointing you in the direction of, a workable solution.
In addition to the query above re buttons in worksheet or form, I would ask:
When you click eg. A twice and then click B , the count for B starts at 1. Does the the count of 2 for A then become irrelevant, back to 0, or needs to be recorded as 2 ?
What action requires that the count is cleared to start afresh?

Below is some revised code that may or may not be going along the right lines.
It assumes that the buttons are in the worksheet.
Assign the Macro 'buttonClicked' to each button

Public numClicks As Integer 'Declare global integer
Public prevBut As String 'Declare global string

Sub buttonClicked()
Dim butName As String
Application.ScreenUpdating = False  'disable screenupdating so's not to reflect selections
ActiveSheet.Shapes(Application.Caller).Select   'select clicked shape to identify button
   butName = Selection.Text   'get button text
    ActiveCell.Select  'Deselect button
 Application.ScreenUpdating = True  'enable screen updating

Select Case butName
    Case prevBut   'same button as previous click
      numClicks = numClicks + 1  'increment this button click count
      '** code to do other stuff here if needed
       
   Case Else   'a different button to previous click
     numClicks = 1   ' registered first click or this button
     '*'** code to do other stuff here if needed*
 End Select
  
 'check clicks and do other stuff asnecessary
If numClicks > 3 Then  'if more than third consecutive click of same button
    MsgBox "The " & butName & "  button has been clicked 3 or more times!"
    'do other stuff if required
    Else
    'If you wish msgbox the count atclicks 1, 2, and 3
    MsgBox numClicks
End If
prevBut = butName  'update the previous button

End Sub

Hope that helps.

The form Buttons are in the worksheet. for example, The " QNB0003" is the form button ID that has a macro assigned to it
So what I mean is whenever I choose from the filter any ID ( I have around 300 IDs) I want the counter to start counting on that selected ID separately the number of clicks that occurred upon it and the message box appears on each click informing me of the number of clicks
also kindly note that I want all of this to be one session, Meaning that if I close the worksheet then all of the counters reset to 0 to avoid accumulation
to elaborate, let's say I have 5 form buttons ( A, B, C, D, E) all within a filter and all have macros assigned to them. When I select from the filter ID " A " and click it I want first to count this click as one in a pop-up message box then the second and third and so on.
Then when I select ID " B " I want its counter to start counting the clicks as it's a separate one and counting the first click , Second , third and so on
The same applies to the rest of the IDs.
and when all is done and close the worksheet i want all of the counter for all of the form buttons to be reset to zero so once reopens it it start afresh

 

[Jaafar Tribak]

One trick you can do in order to substantially shorten the code is to store the accumulated number of clicks in the button's TopLeft cell ID Property.(Assuming each Button has it own separate TopLeft Cell like it is shown on the image you posted)

The Range ID Property is not persistent meaning, it is reset after each session which is convinient in this particular scenario.

Assign this Macro to all your buttons:

Sub Buttons_Macro()
    With ActiveSheet.Buttons(Application.Caller)
        .TopLeftCell.ID = Val(.TopLeftCell.ID) + 1
        MsgBox .Name & vbLf & vbLf & "Number Of Clicks : [" & .TopLeftCell.ID & "]"
    End With
End Sub

Note: This won't work if the worksheet is Protected unless you tick the *Edit Objects* option.

 

[EzzEldean]

@EzzEldean Without a clear definition of what you have and what you want, neither I nor @Flashbond can be confident in providing you with or, pointing you in the direction of, a workable solution.
In addition to the query above re buttons in worksheet or form, I would ask:
When you click eg. A twice and then click B , the count for B starts at 1. Does the the count of 2 for A then become irrelevant, back to 0, or needs to be recorded as 2 ?
What action requires that the count is cleared to start afresh?

Below is some revised code that may or may not be going along the right lines.
It assumes that the buttons are in the worksheet.
Assign the Macro 'buttonClicked' to each button

Public numClicks As Integer 'Declare global integer
Public prevBut As String 'Declare global string

Sub buttonClicked()
Dim butName As String
Application.ScreenUpdating = False  'disable screenupdating so's not to reflect selections
ActiveSheet.Shapes(Application.Caller).Select   'select clicked shape to identify button
   butName = Selection.Text   'get button text
    ActiveCell.Select  'Deselect button
 Application.ScreenUpdating = True  'enable screen updating

Select Case butName
    Case prevBut   'same button as previous click
      numClicks = numClicks + 1  'increment this button click count
      '** code to do other stuff here if needed
      
   Case Else   'a different button to previous click
     numClicks = 1   ' registered first click or this button
     '*'** code to do other stuff here if needed*
 End Select
 
 'check clicks and do other stuff asnecessary
If numClicks > 3 Then  'if more than third consecutive click of same button
    MsgBox "The " & butName & "  button has been clicked 3 or more times!"
    'do other stuff if required
    Else
    'If you wish msgbox the count atclicks 1, 2, and 3
    MsgBox numClicks
End If
prevBut = butName  'update the previous button

End Sub

Hope that help

Yes you are right, will be irrelevent but not to get back to 0 i still want it to store its clicks number which is 2 times. The Action required is to close the sheet and then re open it again.

I tried the code but it gave me this , Any advise?

I am so sorry that I reply late

 

[EzzEldean]

One trick you can do in order to substantially shorten the code is to store the accumulated number of clicks in the button's TopLeft cell ID Property.(Assuming each Button has it own separate TopLeft Cell like it is shown on the image you posted)

The Range ID Property is not persistent meaning, it is reset after each session which is convinient in this particular scenario.

Assign this Macro to all your buttons:

Sub Buttons_Macro()
    With ActiveSheet.Buttons(Application.Caller)
        .TopLeftCell.ID = Val(.TopLeftCell.ID) + 1
        MsgBox .Name & vbLf & vbLf & "Number Of Clicks : [" & .TopLeftCell.ID & "]"
    End With
End Sub

Note: This won't work if the worksheet is Protected unless you tick the *Edit Objects* option.

Thank you for your reply but i tried it but it didn't work and yes i confirmed that the worksheet is not locked

 

[EzzEldean]

One trick you can do in order to substantially shorten the code is to store the accumulated number of clicks in the button's TopLeft cell ID Property.(Assuming each Button has it own separate TopLeft Cell like it is shown on the image you posted)

The Range ID Property is not persistent meaning, it is reset after each session which is convinient in this particular scenario.

Assign this Macro to all your buttons:

Sub Buttons_Macro()
    With ActiveSheet.Buttons(Application.Caller)
        .TopLeftCell.ID = Val(.TopLeftCell.ID) + 1
        MsgBox .Name & vbLf & vbLf & "Number Of Clicks : [" & .TopLeftCell.ID & "]"
    End With
End Sub

Note: This won't work if the worksheet is Protected unless you tick the *Edit Objects* option.

Thank you so much it worked perfectly as I assigned it to the form button on each click the message box appears with the number of clicks but I want your help with something.

I have this code to help me when I click the form button it opens the email ( outlook ) as a template to send an email, So what I want whenever I click the form button that has the macro of " Send Email" assigned to it I want also to count the clicks as well and have the message box appears too.

Basically what I want is to merge or assign the two macros to be able to do two things 1st to send an email and second have the pop-up message informing me of the number of clicks ( i have around 300 macros with the same code as below but different data and names )

Sub SendEmail_QNB0015()
Dim EmailApp As Outlook.Application
Dim Source As String
Set EmailApp = New Outlook.Application
Dim emailitem As Outlook.MailItem
Set emailitem = EmailApp.CreateItem(olMailItem)
emailitem.To = Range("D2").Value
emailitem.CC = Range("D13").Value
emailitem.Subject = "QNB0015"
emailitem.Body = "Dear Team," & vbNewLine & vbNewLine & "Appreciate your support with the below"
emailitem.Display
End Sub

 

[EzzEldean]

Everyone the solution i just found is

Sub CallMacros()
Call Macro1
Call Macro2
Call Macro3
Call Macro4
End Sub

And finally, Thank you all for your help I would like to give my appreciation to Mr. Flashbond @Flashbond, Mr.Snakehips @Snakehips, And Mr.Jaafar Tribak @Jaafar Tribak

Thank you for your full support.

 

[Jaafar Tribak]

Everyone the solution i just found is

Sub CallMacros()
Call Macro1
Call Macro2
Call Macro3
Call Macro4
End Sub

And finally, Thank you all for your help I would like to give my appreciation to Mr. Flashbond @Flashbond, Mr.Snakehips @Snakehips, And Mr.Jaafar Tribak @Jaafar Tribak

Thank you EzzEldean for the appreciation and glad you got it working in the end.

 

[EzzEldean]

Thank you EzzEldean for the appreciation and glad you got it working in the end.

Excuse me, One last thing I'd like to understand and know.
How I can use a condition with your macro that says if the number of clicks reaches 3 another message box appears saying that this is the third time clicking?

As Mr.Snakehips advised with this condition

{ If numClicks > 3 Then 'if more than third consecutive click of same button
MsgBox "The " & butName & " button has been clicked 3 or more times!" }

 

[Jaafar Tribak]

Excuse me, One last thing I'd like to understand and know.
How I can use a condition with your macro that says if the number of clicks reaches 3 another message box appears saying that this is the third time clicking?

As Mr.Snakehips advised with this condition

{ If numClicks > 3 Then 'if more than third consecutive click of same button
MsgBox "The " & butName & " button has been clicked 3 or more times!" }

Sub Buttons_Macro()
    With ActiveSheet.Buttons(Application.Caller)
        .TopLeftCell.ID = Val(.TopLeftCell.ID) + 1
        If .TopLeftCell.ID = 3 Then
            MsgBox .Name & vbLf & vbLf & "You Clicked : [" & .TopLeftCell.ID & "] times."
        End If
    End With
End Sub

 

[EzzEldean]

Sub Buttons_Macro()
    With ActiveSheet.Buttons(Application.Caller)
        .TopLeftCell.ID = Val(.TopLeftCell.ID) + 1
        If .TopLeftCell.ID = 3 Then
            MsgBox .Name & vbLf & vbLf & "You Clicked : [" & .TopLeftCell.ID & "] times."
        End If
    End With
End Sub

As Usual, Thank you.