Change ActiveX Command button color back to previous color after clicked

Question

I have a spreadsheet with over 65 ActiveX Command Buttons. When I left click one command button, it turns green and add a (+1) in a cell. When I right click the same command button, it turns red and add a (+1) in a cell.

When I click another command button, I want to return the previous command button back to the default grey. The issue is that the previous command button remains the same color as I previous clicked.

How do I make the command button that was clicked, return back to default grey, when there are 65+ command buttons on a sheet. Here is what I have so far for a single command button:

Private Sub Action68_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Button = 1 Then
    Worksheets("Stats").Cells(CurrentPlayerRow, "BA").Value = Worksheets("Stats").Cells(CurrentPlayerRow, "BA").Value + 1
    Action68.BackColor = vbGreen
ElseIf Button = 2 Then
    Worksheets("Stats").Cells(CurrentPlayerRow, "BB").Value = Worksheets("Stats").Cells(CurrentPlayerRow, "BB").Value + 1
    Action68.BackColor = vbRed
End If
End Sub

Private Sub Action69_MouseDown(ByVal Button As Integer, ByVal Shift As 
Integer, ByVal X As Single, ByVal Y As Single)

If Button = 1 Then
    Worksheets("Stats").Cells(CurrentPlayerRow, "BT").Value = Worksheets("Stats").Cells(CurrentPlayerRow, "BT").Value + 1
    Action69.BackColor = vbGreen
ElseIf Button = 2 Then
    Worksheets("Stats").Cells(CurrentPlayerRow, "BU").Value = Worksheets("Stats").Cells(CurrentPlayerRow, "BU").Value + 1
    Action69.BackColor = vbRed
End If 
End Sub

I have it where it changes the color to red or green, when it is right or left clicked. But I do not know how to make it change to a default grey, when another button is clicked.

Basically, When I click the 'Action 69' command button, the 'Action68' command button along with the other 67 command buttons, returns to a default grey, so that the color changes only for the button that is clicked. Do you have any suggestions?

Thank you

Answer 1

That's a lot of copy-paste and duplicated code. You will want to reduce that duplication so that the day you need the buttons to do something else (or just to change the color scheme), you have one place to change instead of 70.

You do that by increasing the abstraction level, i.e. by implementing the functionality in a separate, dedicated procedure.

Public Enum ButtonState
    LeftButton = 1
    RightButton = 2
End Enum

Private Sub HandleControlClick(ByVal axControl As MSForms.Control, ByVal column As String, ByVal state As ButtonState)
    Const defaultColor As Long = &H8000000F&
    Dim newColor As Long, columnOffset As Long
    Select Case state
        Case LeftButton
            newColor = vbRed
        Case RightButton
            newColor = vbGreen
            columnOffset = 1
        Case Else
            newColor = defaultColor
    End Select
    axControl.BackColor = newColor
    StatsSheet.Cells(CurrentPlayerRow, column).Offset(0, columnOffset).Value = StatsSheet.Cells(CurrentPlayerRow, column).Offset(0, columnOffset).Value + 1
End Sub

And now your handlers can look like this:

Private Sub Action68_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    HandleControlClick ActiveSheet.OleObjects("Action68").Object, Button, "BA"
End Sub

Private Sub Action69_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    HandleControlClick ActiveSheet.OleObjects("Action69").Object, Button, "BT"
End Sub

I'd warmly recommend you give a (Name) of statsSheet (or similar) to your Worksheets("Stats") if possible - that way you use an already-existing worksheet object instead of fetching it from the Worksheets collection every time.