Change Command Button Characteristics Based on Which Button in a Group is Clicked

J

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon!
I want to change the command button background color, the font size, and make it bold when clicked, and have it remain that color until another button in that particular group of Command Buttons is clicked.
There are three groups on the same sheet.
Basically, if a button in Group A, "SepGB" is clicked, it will change from RGB(0, 50, 110), 9 font, and not bolded to RGB(79, 129, 189), 10 font, and bold and if another button in Group A is clicked, "SepGB" will return to RGB(0, 50, 110), 9 font, and not bolded. HOWEVER, if a button from Groups B or C is clicked, "SepGB" will remain RGB(79, 129, 189), 10 font, and bolded.
The code below is working. It sets it so that every time a button is clicked, the others return to their original state. However, doing this for all 37 command buttons seems tedious (and makes the module huge). The groupings are below the code.

Any ideas are greatly appreciated!
--------------------------------------------------
Private Sub SepGB_Click()

ActiveSheet.Shapes("SepGB_Chart").ZOrder msoBringToFront

ActiveSheet.SepGB.BackColor = RGB(79, 129, 189)

ActiveSheet.SepGB.Font.Bold = True

ActiveSheet.SepGB.Font.Size = 10

ActiveSheet.OctGB.BackColor = RGB(0, 50, 110)

ActiveSheet.OctGB.Font.Bold = False

ActiveSheet.OctGB.Font.Size = 9

ActiveSheet.NovGB.BackColor = RGB(0, 50, 110)

ActiveSheet.NovGB.Font.Bold = False

ActiveSheet.NovGB.Font.Size = 9

ActiveSheet.DecGB.BackColor = RGB(0, 50, 110)

ActiveSheet.DecGB.Font.Bold = False

ActiveSheet.DecGB.Font.Size = 9

ActiveSheet.JanGB.BackColor = RGB(0, 50, 110)

ActiveSheet.JanGB.Font.Bold = False

ActiveSheet.JanGB.Font.Size = 9

ActiveSheet.FebGB.BackColor = RGB(0, 50, 110)

ActiveSheet.FebGB.Font.Bold = False

ActiveSheet.FebGB.Font.Size = 9

ActiveSheet.MarGB.BackColor = RGB(0, 50, 110)

ActiveSheet.MarGB.Font.Bold = False

ActiveSheet.MarGB.Font.Size = 9

ActiveSheet.AprGB.BackColor = RGB(0, 50, 110)

ActiveSheet.AprGB.Font.Bold = False

ActiveSheet.AprGB.Font.Size = 9

ActiveSheet.MayGB.BackColor = RGB(0, 50, 110)

ActiveSheet.MayGB.Font.Bold = False

ActiveSheet.MayGB.Font.Size = 9

ActiveSheet.JunGB.BackColor = RGB(0, 50, 110)

ActiveSheet.JunGB.Font.Bold = False

ActiveSheet.JunGB.Font.Size = 9

End Sub

Private Sub OctGB_Click()

ActiveSheet.Shapes("OctGB_Chart").ZOrder msoBringToFront

ActiveSheet.OctGB.BackColor = RGB(79, 129, 189)

ActiveSheet.OctGB.Font.Bold = True

ActiveSheet.OctGB.Font.Size = 10

ActiveSheet.SepGB.BackColor = RGB(0, 50, 110)

ActiveSheet.SepGB.Font.Bold = False

ActiveSheet.SepGB.Font.Size = 9

ActiveSheet.NovGB.BackColor = RGB(0, 50, 110)

ActiveSheet.NovGB.Font.Bold = False

ActiveSheet.NovGB.Font.Size = 9

ActiveSheet.DecGB.BackColor = RGB(0, 50, 110)

ActiveSheet.DecGB.Font.Bold = False

ActiveSheet.DecGB.Font.Size = 9

ActiveSheet.JanGB.BackColor = RGB(0, 50, 110)

ActiveSheet.JanGB.Font.Bold = False

ActiveSheet.JanGB.Font.Size = 9

ActiveSheet.FebGB.BackColor = RGB(0, 50, 110)

ActiveSheet.FebGB.Font.Bold = False

ActiveSheet.FebGB.Font.Size = 9

ActiveSheet.MarGB.BackColor = RGB(0, 50, 110)

ActiveSheet.MarGB.Font.Bold = False

ActiveSheet.MarGB.Font.Size = 9

ActiveSheet.AprGB.BackColor = RGB(0, 50, 110)

ActiveSheet.AprGB.Font.Bold = False

ActiveSheet.AprGB.Font.Size = 9

ActiveSheet.MayGB.BackColor = RGB(0, 50, 110)

ActiveSheet.MayGB.Font.Bold = False

ActiveSheet.MayGB.Font.Size = 9

ActiveSheet.JunGB.BackColor = RGB(0, 50, 110)

ActiveSheet.JunGB.Font.Bold = False

ActiveSheet.JunGB.Font.Size = 9

End Sub
--------------------------------------------------------
Group A:
SepGR
OctGB
NovGB
DecGB
JanGB
FebGB
MarGB
AprGB
MayGB
JunGB

Group B:
RL8
RL81
RL82
RL84
RL86
RI8
RI81
RI82
RI85
RI86
RI89
W8
W81
W82
W84
W85
W810
L8
L84
L86

Group C:
Pre
BOY
IA1
MOY
IA2
EOY
Pos
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Are you stuck on using commandbuttons? You have more options with shapes such as a single macro for each group.
 
Upvote 0
For the fun of it. :)
Add or use existing buttons with naming convention as given in you post ???GB
Run ExampleSetUpGroup and click away.
You will need to use some sort of consistent naming convention for your other groups' controls or change up the method of determining which buttons belong to which group.

See ShapeGroups.xlsm in this folder.

Code in worksheet:
VBA Code: 
Private ButtonGroupOneCollection As New Collection

Sub ExampleSetUpGroup()
    Dim ButtonGroupOne As ButtonGroup
    Dim oo As OLEObject
  
    For Each oo In OLEObjects
        If TypeOf oo.Object Is CommandButton Then
            If oo.Name Like "???GB" Then
                Set ButtonGroupOne = New ButtonGroup
                Set ButtonGroupOne.Caller = Me
                Set ButtonGroupOne.Button = oo.Object
                ButtonGroupOneCollection.Add ButtonGroupOne
            End If
        End If
    Next
End Sub

Public Sub Group_Click(ButtonObject As MSForms.CommandButton)
    Dim b
  
    ButtonObject.BackColor = RGB(79, 129, 189)
    ButtonObject.Font.Bold = True
    ButtonObject.Font.Size = 10
  
    For Each b In ButtonGroupOneCollection
        If Not b.Button Is ButtonObject Then
            b.Button.BackColor = RGB(0, 50, 110)
            b.Button.Font.Bold = False
            b.Button.Font.Size = 9
        End If
    Next
End Sub

Class module named ButtonGroup:
VBA Code: 
Option Explicit

Public WithEvents Button As MSForms.CommandButton
Public Caller As Object

Private Sub button_Click()
     Caller.Group_Click Button
End Sub
 
Upvote 0
dataluver said:
For the fun of it. :)
Add or use existing buttons with naming convention as given in you post ???GB
Run ExampleSetUpGroup and click away.
You will need to use some sort of consistent naming convention for your other groups' controls or change up the method of determining which buttons belong to which group.

See ShapeGroups.xlsm in this folder.

Code in worksheet:
VBA Code: 
Private ButtonGroupOneCollection As New Collection

Sub ExampleSetUpGroup()
    Dim ButtonGroupOne As ButtonGroup
    Dim oo As OLEObject
 
    For Each oo In OLEObjects
        If TypeOf oo.Object Is CommandButton Then
            If oo.Name Like "???GB" Then
                Set ButtonGroupOne = New ButtonGroup
                Set ButtonGroupOne.Caller = Me
                Set ButtonGroupOne.Button = oo.Object
                ButtonGroupOneCollection.Add ButtonGroupOne
            End If
        End If
    Next
End Sub

Public Sub Group_Click(ButtonObject As MSForms.CommandButton)
    Dim b
 
    ButtonObject.BackColor = RGB(79, 129, 189)
    ButtonObject.Font.Bold = True
    ButtonObject.Font.Size = 10
 
    For Each b In ButtonGroupOneCollection
        If Not b.Button Is ButtonObject Then
            b.Button.BackColor = RGB(0, 50, 110)
            b.Button.Font.Bold = False
            b.Button.Font.Size = 9
        End If
    Next
End Sub

Class module named ButtonGroup:
VBA Code: 
Option Explicit

Public WithEvents Button As MSForms.CommandButton
Public Caller As Object

Private Sub button_Click()
     Caller.Group_Click Button
End Sub
Click to expand...

Perfect! Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
A simple VB Macro to change a list of Toggle Buttons color/caption once another button is clicked.
Replies
1
Views
938
Micron
M
Y
  • Solved
Toggle sort multicolumn listbox with headers as command buttons
Replies
5
Views
1K
Yodelayheewho
Y
X
  • Question
Conditional formatting cells up to first cell after group of consecutive zeros
Replies
1
Views
292
awoohaw
awoohaw
Jaafar Tribak
  • Question
Round Buttons on UserForm (Beveled + Clickable + Tab-able)
2
Replies
17
Views
2K
Jaafar Tribak
Jaafar Tribak
J
  • Question
How to "Undo" Hover Actions in VBA
Replies
2
Views
344
jbiehl
J

Forum statistics

Threads
1,214,652
Messages
6,120,747
Members
448,989
Latest member
mariah3

Share this page

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