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

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
53
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
L

Legacy 456155

Guest
Are you stuck on using commandbuttons? You have more options with shapes such as a single macro for each group.
 
L

Legacy 456155

Guest
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
 

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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

Perfect! Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,337
Messages
5,547,355
Members
410,785
Latest member
phillippaige
Top