Colour Button on Click

Creating a group of Active X command buttons and showing which one is the active button can be handy to demonstrate which procedure has just been clicked. I recently had this problem developing the 2014 World Cup Football Dashboard. The advantage of this technique is being able to easily see which group is the active group.

The following shows command button 2 (Group B) has been coloured yellow. This says that this is the button which has just been clicked. If we click command button 4 (Group D) it will turn yellow and Group B button will turn back to blue.

The task is achieved by setting up a table which has the colour codes for each of the

Firstly I set up a table in Sheet2 which looks like the following.

The headings in the above table Dash Colour 1, 2 and 3 stands for the colour index of the 3 primary colours which makes up the final colour of the final button.  So (0,0,100) is the colour for the blue buttons and (255,193,37) is the colour for the yellow button.

Now that this is known all we need to do is create some formula in the above table to be able to change as each of the buttons is clicked.  The formula is;

=IF(Sheet1!$A$1=ROW(A1),255,0)

As each of the buttons are clicked A1 is populated with numbers from 1 to 8.  In the above formula this is represented by:

=IF(Sheet1!$A$1=ROW(A1)

So if A1=1 then put 255 otherwise put 0 which is the number for the first part of the blue colour.  The same theory is used for the second and third parts.

=IF(Sheet1!$A$1=ROW(A1),193,0)

=IF(Sheet1!$A$1=ROW(A1),37,100)

For each of the 8 command buttons there is a procedure which looks like the following;

Private Sub CommandButton1_Click()
Sheet1.[a1]=1
ChangeColour
End Sub

The above is for Command Button 1 and it is putting 1 in cell A1 then running the Change Colour procedure.  As the 1 is placed in A1 the formula in the above table will produce the result for a yellow button (255,193,37).  This needs to be repeated for all 8 command buttons.

The following is the code for the main procedure;

Option Explicit

Sub ChangeColour() 'Excel VBA to change command button colour.
Dim obj As Object
Dim arr As Variant
Dim sh As Worksheet
Dim i As Integer

arr=Sheet2.Range("B2", Sheet2.Range("D" & Rows.Count).End(xlUp))

For Each obj In ActiveSheet.OLEObjects
If TypeName(obj.Object)="CommandButton" Then
i=i + 1
obj.Object.BackColor=RGB(arr(i, 1), arr(i, 2), arr(i, 3))
End If
Next

End Sub

The code loops through the data table above and re colours each command button based on the colour indicated by the table.  This part of the code RGB(arr(i, 1), arr(i, 2), arr(i, 3)) is the reference to the tables 3 elements.  If you are looking for more colours to use for a similar procedure perhaps the following article Random Button Colour could help.

The following is the file which shows the workings.