Command Button Color Change

RoxyAS

New Member
Joined
Jan 27, 2010
Messages
3
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? :confused:

Thank you for any responses!!! :)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.

Code:
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
 
Last edited:

RoxyAS

New Member
Joined
Jan 27, 2010
Messages
3
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.

Code:
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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

New Member
Joined
Jan 27, 2010
Messages
3
Tried it again and it works!!! Thank you so so much for the help!!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,985
Messages
5,767,444
Members
425,414
Latest member
chwein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top