VBA to change colour of button when pressed

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
211
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!!
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
What type of buttons are they? Form control, ActiveX, or are they shapes rather than a button?
Also what are their names?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
Forgot to ask, what are the colours?
 

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
211
Office Version
  1. 2016
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
211
Office Version
  1. 2016
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!!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

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