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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
VBA Code:
Dim clicked As Integer 'Declare global integer
Sub buttonClicked()
  clicked = clicked + 1  'Function to increase +1
End Sub
Sub Button1_Click()
  buttonClicked 'Here call the function on each button click
  MsgBox clicked
End Sub
 
Last edited by a moderator:
Upvote 0
@EzzEldean Welcome.
Easy enough, in essence, to code your button click events to keep a click tally (See post #2) and react if and when the count reaches 3.
However, the detail as to how you keep that tally will very much depend upon what you have, what you need. Eg how long do you need to keep the tally before it might be lost or refreshed.
Options could include integer or array variables or, maybe values in a table in your workbook, which need not be necessarily be visible.
Hope that helps.
 
Upvote 0
VBA Code:
Dim clicked As Integer 'Declare global integer
Sub buttonClicked()
  clicked = clicked + 1  'Function to increase +1
End Sub
Sub Button1_Click()
  buttonClicked 'Here call the function on each button click
  MsgBox clicked
End Sub
Appreciate your support and feedback, But could you please guide me through this code and how to apply it as I have around 300 form buttons for each ID?
 
Upvote 0
@EzzEldean Welcome.
Easy enough, in essence, to code your button click events to keep a click tally (See post #2) and react if and when the count reaches 3.
However, the detail as to how you keep that tally will very much depend upon what you have, what you need. Eg how long do you need to keep the tally before it might be lost or refreshed.
Options could include integer or array variables or, maybe values in a table in your workbook, which need not be necessarily be visible.
Hope that helps.
Thank you for your feedback and support but could you please elaborate as I am not following up well
 
Upvote 0
VBA Code:
Dim clicked As Integer 'Declare global integer
Sub buttonClicked()
  clicked = clicked + 1  'Function to increase +1
End Sub
Sub Button1_Click()
  buttonClicked 'Here call the function on each button click
  MsgBox clicked
End Sub
Thank you so much for your help.

If you could help me just to have the message box of the number appear each time I click form button instead of running the macro itself that would be a huge help.
 
Upvote 0
VBA Code:
Dim clicked As Integer 'Declare global integer
Sub buttonClicked()
  clicked = clicked + 1  'Function to increase +1
End Sub
Sub Button1_Click()
  buttonClicked 'Here call the function on each button click
  MsgBox clicked
End Sub
one more thing, Sorry to bother you.

the code is counting well but I have multiple form buttons, So I want to reset the counter once I choose another ID

For example, we have 5 form buttons. A, B, C, D, E

Once I select "A" and click it the counter counts 1 and 2 and 3 and so on that's good.
but when I select from the filter "B" I want the counter once I click on "B" to start counting from 1 not accumulated upon the previous clicks.

Any Ideas?
 
Upvote 0
@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.
 
Upvote 0
Where are your buttons? In the worksheet or in a userform?
in the worksheet. the " QNB0003" is the form button 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 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
1669009107779.png
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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