Counts every time a button is clicked.

[Ancient Wolf]

Hello again MrExcel.

I searched the forum, but could not find anything for this issue. I hope you can help.

This time, I am needing to add coding to some macro controlled shapes serving as buttons that will count each time each button has been clicked on.

Right now, I have not specified any location where the counts will be stored. Most likely on a separate sheet that will be hidden when not in use. I also have some coding in the buttons that color the buttons when they are first clicked on, and another button that clears the coloring of each button. I don't know if that would affect the counting so I included the codes just in case.

Changes individual buttons
Dim oShape As Shape
Set oShape = ActiveSheet.Shapes(Application.Caller)
If oShape.Fill.ForeColor.RGB = RGB(255, 0, 0) Then
oShape.Fill.ForeColor.RGB = RGB(0, 0, 255)
Else
oShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
End If

Changes all buttons
Dim oShape As Shape
For Each oShape In ActiveSheet.Shapes
If oShape.Fill.ForeColor.RGB = RGB(255, 0, 0) Then
oShape.Fill.ForeColor.RGB = RGB(0, 0, 255)

End If
Next oShape

 

[Ruddles]

Create a new macro like this:-

Sub CountShape[COLOR=red][B]001[/B][/COLOR]()
  With ThisWorkbook.Sheets("[COLOR=red][B]Counters[/B][/COLOR]")
    .Range("[B][COLOR=red]A1[/COLOR][/B]") = .Range("[COLOR=red][B]A1[/B][/COLOR]").Value + 1
  End With
End Sub

Modify the sheet name and range to suit your purposes and and assign the macro to the shape you want counted.

Create a new macro with a different name for the second shape in your workbook, pointing to the range where you want the count to appear for that shape and assign the macro to the second shape.

Repeat for each shape.

 

[Ancient Wolf]

Thank you, Ruddles, for the quick response. I was gone for a few days so I did not get to try the coding you provided till now. It is working great! I really appreciate it.

 

[Ruddles]

Thanks for the feedback - it's also appreciated.