Counting Clicks?

[bopo]

Hi

I came up with an idea, basically every time a specific button is clicked, I want a counter to count the clicks, But I want a label displaying the number of clicks, this needs to auto update every time a user clicks the button any ideas?

[Bob Phillips]

Here is some code that puts the count in the button caption, based upon a control toolbox button

[vba]

Private Sub CommandButton1_Click()
Static cnt As Long
cnt = cnt + 1
Me.CommandButton1.Caption = "I have been clicked " & cnt & " times"
End Sub
[/vba]

[mdmackillop]

I came up with an idea, basically every time a specific button is clicked, I want a counter to count the clicks

Is the count to be continued between different sessions or restart each time?

[bopo]

Is the count to be continued between different sessions or restart each time?

Forgot to metion that one, it shoulld resart once Excel has closed therefore one session.

[mdmackillop]

In that case, Bob's code should work fine.

[bopo]

I used Bobs example, and adjusted it to a lable, but it does not work

[vba]
Static cnt As Long
cnt = cnt + 1
Form1.lbldisplay.Caption = "_ " & cnt & " clicks "
[/vba]

[lucas]

Hi bopo,
That should work...probably depends on where your putting it.
Form1 must be spelled correctly or use me.
lbldisplay must be identical to the name of your label.
[VBA]
Private Sub CommandButton1_Click()
Static cnt As Long
cnt = cnt + 1
Me.Label1.Caption = "_ " & cnt & " clicks"
End Sub
[/VBA]

[bopo]

I just double check eveything, btw this lable is on a different userform to the button.

[lucas]

I don't know if that can be done bopo....can you post a small workbook with just the 2 userforms. I'm not following what your trying to do.

[Charlize]

I think his label is on another form then the button that is pushed. When cmd_button1_on_form_one is clicked put [VBA]cnt = cnt + 1[/VBA]When you open form2[VBA]me.labelonformtwo.value = "clicked : " & cnt[/VBA]
Does cnt needs to be public ?

Charlize

[CBrine]

Here's a copy of charlize's idea implemented on a worksheet.

Charlize,
The global varailable was needed(Or some other method of storage) to keep the count value.

HTH
Cal

[Brandtrock]

When I read the bopo's request, I took it to mean that a button would be clicked on one form then the number of clicks would be displayed on another. I 'yoinked' Cal's solution and tweaked it so the button can be clicked X number of times then when the Continue button is pressed the label on UserForm2 displays the number of clicks. The variable cnt is stored in the Tag of the UserForm1 CommandButton1 and read by Label1 (named "Clicks") on UserForm2.

Closing UserForm2 leaves UserForm1 visible again and the button may be clicked more. Once UserForm1 is dismissed and reactivated, the Tag is set to 0 so it may start fresh.

Regards,

[matthewspatrick]

Off topic detour...

Almost two years ago, a person hired me to build code that would count the number of times CommandBarButtons were clicked on a toolbar. I (somewhat clumsily!) implemented a class to trap the Click event for those CommandBarButtons, and write the updated stats to the Registry (he wanted to count the total history of clicks).

Sounds simple, right? Problem was, for some strange reason the Click event would fire several times for a single mouse click! Sometimes as many as four times!

I got around it by using a Registry entry to hold the last CommandBarButton that was clicked and the time of clicking, and changed my event code so that it would exit if the CommandBarButton reference were the same and the time last clicked was fewer than 3 seconds or so.

The work was done in Excel 2000. Has anyone else seen anything remotely like it, or was it probably the residue of my class module fumbling? (Since it was work for hire, I am not at liberty to post the code.)