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

EzzEldean

New Member
Joined
Nov 17, 2022
Messages
18
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
  6. 2003 or older
Platform
  1. Windows
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 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

VBA Code:
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
1669009107779.png
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
VBA Code:
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.
 
Last edited:
Upvote 0
Solution
@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

VBA Code:
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
1669271482934.png
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
Upvote 0
Thank you EzzEldean for the appreciation and glad you got it working in the end.(y)
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!" }
 
Upvote 0
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!" }
VBA Code:
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
 
Upvote 0
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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