Colored buttons when pressed (groups of buttons)

[Artiell]

Hey,

I’m quite new to VBA, although I’ve used Excel for years now. I’m currently building up a form of service calculator for a company in Excel, and this time, decided to do it via VBA (for the UI to look smooth with dynamic buttons, and to practice my own VBA). The calculator works as following: Four categories, say A, B, C and D, which each have some given parameters in them.

For example: C has 4 Buttons, each with an own value, which is then added to a running total. A has two buttons, etc.

My problem is, that I’d like for a button to be colored (highlighted) when pressed on, in its corresponding category. So I’d like for A to only have one colored button (if pressed on, otherwise blank), B to only have one colored button, C to only have one colored button and same with D. I’ve used the following code (found from a thread on this forum), but it resets a colored button when pressing on another button in an other category.:

Sub SetColor (v)
Dim cell As String
Dim shp As Shape
cell = Active.Cell.Address
ActiveSheet.Shapes.SelectAll
Selection.ShapeRange.Fill.Forecolor.RGB = RGB(255, 255, 255)
ActiveSheet.Shapes(v).Fill.Forecolor.RGB = RGB(128, 128, 128)
Range(cell).Activate

End Sub

And in each macro to activate the color:

Call SetColor(Application.Caller)

To sum it up, I’m looking for some piece of code (or a modification to the current one), such that when a button is pressed in one category, it stays highlighted when a button is pressed in another category (but if a button is pressed in the same category, the new button is highlighted instead of the old one. I suspect the ’SelectAll’ part in the code is what is causing me the problem, but I’m not sure.

Thank you!
Artiell

 

[DanteAmor]

Hi and welcome to MrExcel!

The calculator works as following: Four categories, say A, B, C and D, ... For example: C has 4 Buttons, ... A has two buttons, etc.

You need to identify which buttons belong to each category. For example, in the following code, I identify that the 1,2,3, and 4 buttons belong to the "A" category. Then those buttons should call the macro "CategoryA"

Sub SetColor(v, category As String)
  Dim a() As Variant
  Select Case category
    Case "A": a = Array("Button1", "Button2", "Button3", "Button4") 'Here you must put the names of your buttons
    Case "B": a = Array("Button5", "Button6", "Button7", "Button8")
    Case "C": a = Array("Button9", "Button10", "Button11", "Button12")
    Case "D": a = Array("Button13", "Button14", "Button15", "Button16")
  End Select
  With ActiveSheet.Shapes
    .Range(a).Fill.ForeColor.RGB = RGB(255, 255, 255)
    .Range(v).Fill.ForeColor.RGB = RGB(128, 128, 128)
  End With
End Sub

Sub CategoryA()
  'Category A buttons should call this macro.
  Call SetColor(Application.Caller, "A")
End Sub
Sub CategoryB()
  'Category B buttons should call this macro.
  Call SetColor(Application.Caller, "B")
End Sub
Sub CategoryC()
  Call SetColor(Application.Caller, "C")
End Sub
Sub CategoryD()
  Call SetColor(Application.Caller, "D")
End Sub

 

[DanteAmor]

It can be simplified as follows:

Sub SetColor(v, a As Variant)
  ActiveSheet.Shapes.Range(Split(a, ",")).Fill.ForeColor.RGB = RGB(255, 255, 255)
  ActiveSheet.Shapes.Range(v).Fill.ForeColor.RGB = RGB(128, 128, 128)
End Sub

Sub CategoryA()
  Call SetColor(Application.Caller, "Button1,Button2,Button3,Button4")
End Sub
Sub CategoryB()
  Call SetColor(Application.Caller, "Button5,Button6,Button7,Button8")
End Sub
Sub CategoryC()
  Call SetColor(Application.Caller, "Button9,Button10,Button11,Button12")
End Sub
Sub CategoryD()
  Call SetColor(Application.Caller, "Button13,Button14,Button15,Button16")
End Sub

Note: Name of buttons without spaces: "Button1,Button2,Button3,Button4"