How to change color of macro button in Excel

Chris Maddogz

unread,

May 21, 2009, 3:41:00 PM5/21/09

to

I have a spreadsheet with 7 lovely macro buttons on it that automatically
execute lots of important jobs but look pretty boring in their basic
colouring ( coloring) and border formatting & I would like to change their
individual background colours ( colors) and bordering so as to make them more
appealing to the users.
Any help would be appreciated

Jacob Skaria

unread,

May 21, 2009, 3:57:01 PM5/21/09

to

If it is command button Right click>Properties>BackColor...select a color (in
Design mode)
OR
If it is a autoshape like (rectangle) Right click >Format Autoshape>Color
and Lines. Pick your color

If this post helps click Yes
---------------
Jacob Skaria

Chris Maddogz

unread,

May 21, 2009, 5:58:01 PM5/21/09

to

orry but after reading other post s on the same subject I have realised that
having created the buttons from the Forms Menu I can't change the colour etc.
However is there a way to use a button created via the Toolbox Menu to
utilise the same code that is assigned to one of my Forms Menu buttons or do
I need to start all over?

Jacob Skaria

unread,

May 21, 2009, 8:19:00 PM5/21/09

to

If you mean command buttons from toolbox in Design mode(xl2003). Select the

button Right click>Properties>BackColor...select a color

If this post helps click Yes

Gord Dibben

unread,

May 22, 2009, 12:33:15 AM5/22/09

to

Assuming your Forms buttons had macros assigned to them and these macros
live in a general module................

When you create the ActiveX button right-click and "View Code".

A sheet module will open with these lines

Private Sub CommandButton1_Click()

End Sub

Place a macro name between the lines like so............

Private Sub CommandButton1_Click()
macroname
End Sub

Gord Dibben MS Excel MVP

unread,

Mar 4, 2016, 11:45:23 PM3/4/16

to

Brilliant suggestion using the autoshape, exactly the solution I needed.

1. 05-11-2017, 11:43 PM #1

   Forum Contributor

   

   ---

   Change color of macro button based on cell value

   I have a macro button called "Button 1".

   I want it to automatically turn green if/when the boolean value in cell R2 is TRUE, and I want it to automatically turn red if/when the boolean value in cell R2 is FALSE.

   I want this behavior to happen without any need to click the button. In fact, the auto changing color is the signal that the user needs to click on the button.

   That's it.

   I have seen related threads on this site, but after hours of trying, I can't adapt the proposed answers there to my situation.

   Please be aware that I am a VBA noob, and need really clear instructions, and the thought of using an Active X control is very, very intimidating because every thread about Active X assumes some familiarity or seems to leave out a step.

   Thanks everyone!

   Jay USA

   ---

2. 05-12-2017, 05:28 AM #2

   Re: Change color of macro button based on cell value

   You cannot change the color of a Forms button. For that you do need an ActiveX button.
   And to have the color changed automatically you do need a little piece of vba. The change event of the sheet could be used for that.
   Also when using an ActiveX button your macro would need to be moved the the button click_event.

   Though this may seem intimidating to you, all that really isn't rocket science and can be explained clearly.
   If you want to procedd I can guide you to make these changes or if you're comfortable you can upload the workbook Then I'll make these changes for you.

   For you to get an idea of what it's all about I'm attachig a sample workbook.
   The sheet has an ActiveX button and a formula in cell R2.
   The formula looks if the value of cell K2 is even. If so it returns True else it returns false.
   The ActiveX button changes color in accordance with the state of cell R2.

   To see what's going on where, open the Developer tab on the ribbon and click View Code.
   If you don't have the Developer tab, right-click on any other ribbon menu and select Customize Ribbon. Then check the Developer checkbox.

   Cheers!
   Tsjallie

   --------

   If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

   If you think design is an expensive waste of time, try doing without ...

   ---

3. 05-17-2017, 07:15 PM #3

   Forum Contributor

   ---

   Re: Change color of macro button based on cell value

   Tsjallie,

   This is a wonderful, wonderful reply that fully solves my problem! Thank you for your encouragement, the clear structure your provided, and the accurate detail you shared. Excellent!

   JayUSA

   ---

4. 05-18-2017, 04:05 AM #4

   Re: Change color of macro button based on cell value

   Glad I could help. Thx for the rep.

   
   To finalize this thread pls mark it SOLVED (see Tread Tools in the menu above).
   Unless you have more questions related to the subject of this thread.

   ---

How do you change the color of the VBA button in Excel?

How do I add color to a macro in Excel?

How do I change a macro button in Excel?

How do I customize a macro button?