Command Button, Fill Command (Disjointed Cell References)

[DaxStax]

I have a 52x52 matrix of cells that I use as a visual aid.

Part 1:

I want each of 52 Command Buttons to "Fill" a disjointed series of cells with a specific color.

For example, when I "Click" "Command Button 1", I wanted cells: =A1, B2, D3:D8, J8 to change from RGB(242,242,242) to RGB(237, 125, 49).

When I "Click" "Command Button 1" again, I want the same cells to return to RGB(242,242,242).

Part 2:

I need to prioritize the command of one button over the command of another ONLY for cell conflicts.

For example, if Buttons X & Y are clicked and the two Buttons reference one or more of the same cells in addition to a series of different cells, I want the command of Button X to supersede the command of Button Y ONLY for the cells that are the same that X & Y reference.

So, if Button X is programmed to change cells A1, B1, and C1 to Red; and Button 2 is programmed to change cells A1, A2, and A3 to Blue: cells A1, B1, and C1 will be Red and A2 and A3 will be Blue when both buttons are selected and I will not receive an error.

When Button X is deselected and Button Y remains selected, cell A1 will turn Blue and cells B1 and C1 will return to their original color (not Red or Blue).

---------------------------------------------------------------

I would like to thank all of you ahead of time for tackling this problem and Mr Excel for his forum.

 

[My Aswer Is This]

You have:
I want each of 52 Command Buttons

Wow. Your going to write scripts for 52 different command buttons.
You have a big job ahead of you.

 

[My Aswer Is This]

I know of no way to have one button with a script look into another buttons script and see what it is programmed to do.

 

[tonyyy]

DaxStax,

You might give the following a try...

Option Explicit
Option Base 1

Sub AddCheckboxes()
Dim ChBox As CheckBox
Dim r As Range
Dim ws As Worksheet

Set ws = ActiveSheet
ActiveSheet.CheckBoxes.Delete
For Each r In Range("A1:A52")
    Set ChBox = ws.CheckBoxes.Add(Left:=r.Left + 3, Top:=r.Top, Width:=r.Width, Height:=r.Height)
    With ChBox
        .Caption = "CheckBox" & r.Row
        .Value = False
        .OnAction = "PrioritizeColors"
        .Name = "CheckBox" & r.Row
    End With
Next r
End Sub


Sub PrioritizeColors()
Application.ScreenUpdating = False
Dim ChBox As CheckBox
Dim arr() As Variant
Dim i As Long, j As Long

Range("A1:BA52").Interior.Color = RGB(242, 242, 242)
i = 0
For Each ChBox In ActiveSheet.CheckBoxes
    If ChBox = 1 Then
        i = i + 1
        ReDim Preserve arr(1, i)
        arr(1, i) = ChBox.Name
    End If
Next ChBox
For j = i To 1 Step -1
    ActiveSheet.CheckBoxes(arr(1, j)).Value = True
    Call ChangeColors(ActiveSheet.CheckBoxes(arr(1, j)).Name)
Next j
Application.ScreenUpdating = True
End Sub


Sub ChangeColors(CkBox As String)
Application.ScreenUpdating = False
Select Case CkBox
Case "CheckBox1"
    If ActiveSheet.CheckBoxes(CkBox).Value = 1 Then _
        Range("A1, B2, D3:D8, J8").Interior.Color = RGB(237, 125, 49)
Case "CheckBox2"
    If ActiveSheet.CheckBoxes(CkBox).Value = 1 Then _
        Range("A1, B1, C1").Interior.Color = RGB(255, 0, 0)
Case "CheckBox3"
    If ActiveSheet.CheckBoxes(CkBox).Value = 1 Then _
        Range("A1, A2, A3").Interior.Color = RGB(0, 0, 255)
Case "CheckBox4"
    If ActiveSheet.CheckBoxes(CkBox).Value = 1 Then _
        Range("D21:G24").Interior.Color = RGB(255, 0, 255)
Case "CheckBox5"
Case "CheckBox6"
Case "CheckBox7"
Case "CheckBox8"
Case "CheckBox9"
Case "CheckBox10"
Case "CheckBox11"
Case "CheckBox12"
Case "CheckBox13"
Case "CheckBox14"
Case "CheckBox15"
Case "CheckBox16"
Case "CheckBox17"
Case "CheckBox18"
Case "CheckBox19"
Case "CheckBox20"
Case "CheckBox21"
Case "CheckBox22"
Case "CheckBox23"
Case "CheckBox24"
Case "CheckBox25"
Case "CheckBox26"
Case "CheckBox27"
Case "CheckBox28"
Case "CheckBox29"
Case "CheckBox30"
Case "CheckBox31"
Case "CheckBox32"
Case "CheckBox33"
Case "CheckBox34"
Case "CheckBox35"
Case "CheckBox36"
Case "CheckBox37"
Case "CheckBox38"
Case "CheckBox39"
Case "CheckBox40"
Case "CheckBox41"
Case "CheckBox42"
Case "CheckBox43"
Case "CheckBox44"
Case "CheckBox45"
Case "CheckBox46"
Case "CheckBox47"
Case "CheckBox48"
Case "CheckBox49"
Case "CheckBox50"
Case "CheckBox51"
Case "CheckBox52"
End Select
Application.ScreenUpdating = True
End Sub

This approach utilizes CheckBoxes rather than Buttons, as it's visually easier (for me anyway) to see which CheckBox is on or off.

The first macro - AddCheckboxes - creates 52 CheckBoxes in Column A. You'll only need to run this once.

The second macro - PrioritizeColors - is run whenever a Checkbox is checked or unchecked. As its name implies, this macro gathers all the checked CheckBoxes, and prioritizes them - with the lowest numbered CheckBox having the highest priority.

The third macro - ChangeColors - is called from PrioritizeColors and assigns the designated color to the designated range. Your examples were used to assign colors/ranges for CheckBoxes 1, 2 and 3. You'll need to complete the assignments for all 52 CheckBoxes. It's assumed the original color for all cells in the matrix is RGB(252,252,252).

All three macros should be placed into a single module.

Cheers,

tonyyy

 

[My Aswer Is This]

Wow. That's a lot of work you did Tony. Will monitor this thread to see how this works out for the OP.
This is way beyond my Knowledgebase.