VBA to change colour of button when pressed

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
282
Office Version
  1. 2016
I have 10 buttons (like a keypad), each with assigned macro to do certain things. I'd like a line of code that I can add to each button's macro that changes the colour of the button when pressed. Idea being, when the button is pressed, it becomes a different colour and the remainder of the code then happens. The button would then need to revert back to its original colour when another of the 10 buttons is pressed.

Hoping this is an easy one!!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
What type of buttons are they? Form control, ActiveX, or are they shapes rather than a button?
Also what are their names?
 
Upvote 0
Forgot to ask, what are the colours?
 
Upvote 0
They are shapes, and are simply labelled individually from 1-10. The worksheet the buttons are on is called "25 Players". The buttons are currently RGB (175,171,171) and when depressed, it'd be great if they could be RGB (224,255,124) - and all with black font.

A potential work-around could be assigning a time limit in the line of code, so the colour only changes for 3 seconds before reverting back. My thinking is the line of code could then be applied anywhere because it wouldn't be reliant on any other buttons. Just a thought.
 
Upvote 0
In each macro put this line at the beginning.
Call SetColor(Application.Caller)

For example:
VBA Code:
Sub macro1()
  Call SetColor(Application.Caller)
  'your code
End Sub
Sub macro2()
  Call SetColor(Application.Caller)
  'your code
End Sub

Add the setcolor macro in a module.
VBA Code:
Sub SetColor(v)
  Dim cell As String
  cell = ActiveCell.Address
  ActiveSheet.Shapes.SelectAll
  Selection.ShapeRange.Fill.ForeColor.RGB = RGB(175, 171, 171)
  ActiveSheet.Shapes(v).Fill.ForeColor.RGB = RGB(224, 255, 124)
  Range(cell).Activate
End Sub
 
Upvote 0
Hi there, I'm very new to VBA (but trying to learn quickly). How do I make the above work? I'm guessing macro1 refers to the first of my button shapes, but how?

I understand how to add a module but don't understand what to do with the first part with macro1, macro2 etc.

Sorry for the troubles!!
 
Upvote 0
I'm guessing macro1 refers to the first of my button shapes
That's right.
And macro2 is for your button 2 and so on.
And you add my last macro to the end of all your macros.

I put "macro1" because I don't know the name of your macros.
So in the macro of your first button you add this line:

Call SetColor(Application.Caller)

And this is an example of what your macro would look like from that first button:
VBA Code:
Sub macro1()
  Call SetColor(Application.Caller)
  'your code
End Sub

If it is too complicated for you, then put here the code of all your macros and I will help you to adjust all the codes.
 
Upvote 0
That's right.
And macro2 is for your button 2 and so on.
And you add my last macro to the end of all your macros.

I put "macro1" because I don't know the name of your macros.
So in the macro of your first button you add this line:

Call SetColor(Application.Caller)

And this is an example of what your macro would look like from that first button:
VBA Code:
Sub macro1()
  Call SetColor(Application.Caller)
  'your code
End Sub

If it is too complicated for you, then put here the code of all your macros and I will help you to adjust all the codes.
Hey! I'm also pretty new to this, and this code seems to be exactly what I needed, However, I when I apply it it changes all the shapes on the sheet to the base color, but I just need it to change a specific selection of the shapes. (The one selected shape is changing to the alternate color - no problem there, just the rest). Is that possible? I've been messing with ShapeRange and Array and can't seem to do it. How would I change your code above to only change specific shapes, not all.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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
Back
Top