单击后将 ActiveX 命令按钮颜色更改回之前的颜色

我有一个包含超过 65 个 ActiveX 命令按钮的电子表格。当我左键单击一个命令按钮时，它会变成绿色并在单元格中添加一个 (+1)。当我右键单击同一个命令按钮时，它会变成红色并在单元格中添加 (+1)。

当我单击另一个命令按钮时，我想将上一个命令按钮恢复为默认灰色。问题是上一个命令按钮的颜色与我之前单击的颜色相同。

当工作表上有超过 65 个命令按钮时，如何使单击的命令按钮返回到默认灰色。这是迄今为止我对单个命令按钮的了解：

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

我有它，当右键单击或左键单击时，它将颜色更改为红色或绿色。但我不知道当单击另一个按钮时如何将其更改为默认灰色。

基本上，当我单击“Action 69”命令按钮时，“Action68”命令按钮以及其他 67 个命令按钮将返回到默认灰色，以便仅针对单击的按钮更改颜色。你有什么建议吗？

谢谢

这是大量的复制粘贴和重复的代码。您将希望减少重复，以便当您需要按钮执行其他操作（或只是更改配色方案）时，您可以更改一个位置而不是 70 个位置。

您可以通过增加抽象层次，即通过在单独的专用过程中实现功能。

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

现在你的处理程序看起来像这样：

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

我热烈推荐您给(Name) of statsSheet（或类似）到你的Worksheets("Stats")如果可能的话 - 这样您就可以使用已经存在的工作表对象，而不是从Worksheets每次都收藏。