sheet with many command buttons, one must be coloured

[robertvdb]

I have a Sheet with multiple command buttons, 12 of them indicate the months of the year. When I press either of those 12, it must become orange. ALL the others must stay in grey.

Is there an easy way to do this ? Thanks.

 

[6StringJazzer]

Are they Forms buttons or ActiveX buttons?

 

[robertvdb]

Are they Forms buttons or ActiveX buttons?

Active X

 

[6StringJazzer]

Simplest but not most compact:

Use one sub to set all buttons back to gray, another sub to update the one that was pressed. Replace the button names here with your actual button names. Adjust colors as desired.

Public Sub SetGray()

   button1.BackColor = RGB(66, 66, 66)
   Button2.BackColor = RGB(66, 66, 66)
   Button3.BackColor = RGB(66, 66, 66)
   Button4.BackColor = RGB(66, 66, 66)
   Button5.BackColor = RGB(66, 66, 66)
   Button6.BackColor = RGB(66, 66, 66)
   Button7.BackColor = RGB(66, 66, 66)
   Button8.BackColor = RGB(66, 66, 66)
   Button9.BackColor = RGB(66, 66, 66)
   Button10.BackColor = RGB(66, 66, 66)
   Button11.BackColor = RGB(66, 66, 66)
   Button12.BackColor = RGB(66, 66, 66)

End Sub

' Repeat the following sub for each button
Public Sub Button1_Click()

   SetGray
   button1.BackColor = RGB(198, 89, 17)

End Sub

 

[Jaafar Tribak]

Another way:

Sub UpdateButtonsColors()
    Dim oBtn As OLEObject, oAcc As IAccessible
    For Each oBtn In ActiveSheet.OLEObjects
        With oBtn
            If TypeOf .Object Is CommandButton Then
                If Not IsError(Evaluate("=MONTH(1&""" & .Object.Caption & """)")) Then
                    Set oAcc = .Object
                    If oAcc.accState(0&) = &H100004 Then
                        .Visible = False
                        .Object.BackColor = RGB(198, 89, 17)
                        .Visible = True
                    Else
                        .Object.BackColor = &H8000000F
                    End If
                End If
            End If
        End With
    Next
End Sub

Then call UpdateButtonsColors in each button click event.

 

[robertvdb]

Thanks Jaafar, your solution works fine. Also thanks to Jeff.