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

You have that code duplicated 70 times? You might want to extract a little parameterized method here... FWIW the default `BackColor` for `ButtonFace` system color is `&H8000000F&` - you can find out by looking at the *properties* toolwindow. — Mathieu Guindon, Sep 23 '17 at 16:55
Thank you for your input. Yes, I have it 70 times for each individual button. I am not following you. Sorry I am new to vba. Would that allow me to have it automatically return to 'default grey' if another button is clicked? — S.Clancy, Sep 23 '17 at 17:04
Either use a global variable which holds the object (button) or run all buttons. Like **Button klicked -> change button in variable to default -> execute button action -> set variable to actual button** — Dirk Reichel, Sep 23 '17 at 17:04
@DirkReichel ActiveX controls raise events, that must each be handled by their own separate handlers. That solution would work with "Form Controls", not ActiveX. — Mathieu Guindon, Sep 23 '17 at 17:10
CurrentPlayerRow is just the name of the command button. I am trying to create a basketball application. I have 12 buttons with basketball players names in it labeled "CurrentPlayerRow", then I have 56 buttons with actions. So I click the player's command button, then the action command button, and it inputs the value on the player's row. Think of it like a stat sheet. — S.Clancy, Sep 23 '17 at 17:30
No. As far as VBA understands it, the `Name` of the command button is `Action68`. — Mathieu Guindon, Sep 23 '17 at 17:31
Thank you to the two of you. I am not sure of the coding that you are talking about, Dirk. I am new to this so I am not following you. Do you have an example code that you would use for something like this? — S.Clancy, Sep 23 '17 at 17:33
@MatsMug Let's assume the global variable `LastButton As Object` then inside the `Action68_MouseDown`-event you use `Set LastButton = Action68` as last line. The first line could be `LastButton.BackColor = ...`. I don't know why that shouldn't work — Dirk Reichel, Sep 23 '17 at 18:11

score 3 · Accepted Answer · answered Sep 23 '17 at 17:37

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.

...or if all the code is in the worksheet module you can use `Me` to refer to the worksheet. — Tim Williams, Sep 23 '17 at 20:00

score 2 · Answer 2 · answered Sep 25 '17 at 02:19

here is some demo code to use only one event handler for all of the buttons on a worksheet

.

put this into class module named BtnClass

this is an event handler for all the buttons on the worksheet

' --------------------------------------------------------------------------------------

Option Explicit

Public WithEvents ButtonGroup As MSForms.CommandButton

Private Sub ButtonGroup_Click()
    Dim msg As String

    msg = "clicked : " & ButtonGroup.Name & vbCrLf _
        & "caption : " & ButtonGroup.Caption & vbCrLf _
        & "top     : " & ButtonGroup.Top & vbCrLf _
        & "left    : " & ButtonGroup.Left

    Debug.Print ButtonGroup.Name; vbNewLine; msg

End Sub

Private Sub ButtonGroup_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    Debug.Print "down", Button, ButtonGroup.Name
    If Button = 1 Then
        ButtonGroup.BackColor = vbRed
        ButtonGroup.TopLeftCell.Offset(0, 3).Interior.Color = vbBlue
    Else
        ButtonGroup.BackColor = vbGreen
        ButtonGroup.TopLeftCell.Offset(0, 3).Interior.Color = vbYellow
    End If
End Sub

Private Sub ButtonGroup_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    Debug.Print "up", ButtonGroup.Name
    ButtonGroup.BackColor = &H8000000F
End Sub

' --------------------------------------------------------------------------------------

put this into the sheet module

' --------------------------------------------------------------------------------------

Private Sub Worksheet_Activate()
    activateButtons
End Sub

' --------------------------------------------------------------------------------------

put this into module

makeButtons creates a bunch of buttons on worksheet

activateButtons attaches the buttons to the class event handler

' --------------------------------------------------------------------------------------

Option Explicit

Dim Buttons() As New BtnClass

Const numButtons = 20
'

Sub doButtons()
    makeButtons         ' does not work reliably ... buttons out of sequence
    activateButtons     ' does not activate reliably (run these separately instead) 
End Sub

Sub makeButtons()       ' creates a column of commandButtons

    Dim sht As Worksheet
    Set sht = ActiveSheet

    Dim i As Integer
    For i = 1 To sht.Shapes.Count
    '    Debug.Print sht.Shapes(1).Properties
        sht.Shapes(1).Delete
        DoEvents
    Next i

    Dim xSize As Integer:    xSize = 2      ' horizontal size (number of cells)
    Dim ySize As Integer:    ySize = 2      ' vertical size

    Dim t As Range
    Set t = sht.Range("d2").Resize(ySize, xSize)

    For i = 1 To numButtons
        sht.Shapes.AddOLEObject Left:=t.Left, Top:=t.Top, Width:=t.Width, Height:=t.Height, ClassType:="Forms.CommandButton.1"
        DoEvents
        Set t = t.Offset(ySize)
    Next i

End Sub

Sub activateButtons()       ' assigns all buttons on worksheet to BtnClass.ButtonGroup

    Dim sht As Worksheet
    Set sht = ActiveSheet

    ReDim Buttons(1 To 1)

    Dim i As Integer
    For i = 1 To sht.Shapes.Count

        ReDim Preserve Buttons(1 To i)
        Set Buttons(i).ButtonGroup = sht.Shapes(i).OLEFormat.Object.Object

    Next i

End Sub

' --------------------------------------------------------------------------------------

Change ActiveX Command button color back to previous color after clicked

2 Answers2

Linked