Command Button Color Change

[RoxyAS]

Hello.

I have created a model where I have 12 command buttons (one for each month).

These command buttons have a macro attached which runs a specific operation for that given month.

I would like that when the month is selected, that the button change color- maybe yellow- so that the person using the model understands that that is the month being analyzed.

When another month is clicked, I would like that button to then change color and the other one to go back to grey.

Any ideas or codes on how I can do this?

Thank you for any responses!!!

 

[MickG]

Hi, Try this:-
NB:-The sub is placed on the sheet Module along with the various Codes for the Commanbuttons.
Change the CommandButton name for each Command Button in the Sun call "Cbut" as shown.

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
Call Cbut("CommandButton1")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
''''''''''''''''''''
Private [COLOR=navy]Sub[/COLOR] CommandButton2_Click()
Call Cbut("CommandButton2")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
''''''''''''''''''
Private [COLOR=navy]Sub[/COLOR] CommandButton3_Click()
Call Cbut("CommandButton3")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] CommandButton4_Click()
Call Cbut("CommandButton4")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]'''''''''''''''''''''[/COLOR]
[COLOR=navy]Sub[/COLOR] Cbut(Cbar [COLOR=navy]As[/COLOR] String)
[COLOR=navy]Dim[/COLOR] Ctrl [COLOR=navy]As[/COLOR] OLEObject, ctr [COLOR=navy]As[/COLOR] OLEObject
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ctrl [COLOR=navy]In[/COLOR] OLEObjects
      [COLOR=navy]If[/COLOR] TypeName(Ctrl.Object) = "CommandButton" [COLOR=navy]Then[/COLOR]
         [COLOR=navy]If[/COLOR] Ctrl.Name = Cbar [COLOR=navy]Then[/COLOR]
            OLEObjects(Ctrl.Name).Object.BackColor = RGB(255, 255, 0)
        [COLOR=navy]Else[/COLOR]
            OLEObjects(Ctrl.Name).Object.BackColor = RGB(204, 204, 204)
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Ctrl
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[RoxyAS]

Hi, Try this:-
NB:-The sub is placed on the sheet Module along with the various Codes for the Commanbuttons.
Change the CommandButton name for each Command Button in the Sun call "Cbut" as shown.

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
Call Cbut("CommandButton1")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
''''''''''''''''''''
Private [COLOR=navy]Sub[/COLOR] CommandButton2_Click()
Call Cbut("CommandButton2")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
''''''''''''''''''
Private [COLOR=navy]Sub[/COLOR] CommandButton3_Click()
Call Cbut("CommandButton3")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] CommandButton4_Click()
Call Cbut("CommandButton4")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]'''''''''''''''''''''[/COLOR]
[COLOR=navy]Sub[/COLOR] Cbut(Cbar [COLOR=navy]As[/COLOR] String)
[COLOR=navy]Dim[/COLOR] Ctrl [COLOR=navy]As[/COLOR] OLEObject, ctr [COLOR=navy]As[/COLOR] OLEObject
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ctrl [COLOR=navy]In[/COLOR] OLEObjects
      [COLOR=navy]If[/COLOR] TypeName(Ctrl.Object) = "CommandButton" [COLOR=navy]Then[/COLOR]
         [COLOR=navy]If[/COLOR] Ctrl.Name = Cbar [COLOR=navy]Then[/COLOR]
            OLEObjects(Ctrl.Name).Object.BackColor = RGB(255, 255, 0)
        [COLOR=navy]Else[/COLOR]
            OLEObjects(Ctrl.Name).Object.BackColor = RGB(204, 204, 204)
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Ctrl
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you for your idea!

Just one thing- I need to debug this line somehow:

For Each Ctrl In OLEObjects

No clue what to do to fix it?

 

[MickG]

Hi, What was the error msg ??
If you pasted the whole code in a new sheet (Right Click sheet Tab, Select code, Vb Window appears, Paste code here) and added 4 ActiveX CommandButtons (1 to 4) and then clicked any of the Buttons the one you clicked should turn "Yellow" and the others "Default".
What have you got thats different from that. !!
Mick

 

[RoxyAS]

Tried it again and it works!!! Thank you so so much for the help!!!!