Random Button Colour with Excel VBA

Creating a group of Active X command buttons and showing a different colour each time is a bit of a novelty but adds a bit of pizzazz to a procedure.  To do so I will draw on a procedure demonstrated in the List Unique Items with VBA article.  The procedure also draws on the colour of buttons in this article Colour Button on Click which isolates one colour.  This procedure could be used inside that one using the different colours shown below.  The following is a pictorial example of the procedure.

Random Colour button
 

The following YouTube video explains how to incorporate this technique into a standard Excel workbook.

Colour Buttons.xlsm

 
 

Each time the Blue button is clicked the colour of the buttons below will change.  The first thing I need to do is to set up a table with the numbers which represent the above colours.

VBA random button colour

The table above is has Vlookup formula which changes based on the Random Number Generator.    For the number generator there needs to be a start number (1) and an end number in this case 12.  The following is the code for the main procedure;

Option Explicit

Private Sub ProduceUniqRandom() 'Excel VBA to generate random number for button colour.
Dim myStart As Long
Dim myEnd As Long
Dim i As Long
Dim a()
Dim sh As Worksheet

Set sh=Sheet1
myStart=sh.[G2]
myEnd=sh.[H2]
ReDim a(0 To myEnd - myStart)

With Createobject("System.Collections.SortedList")
Randomize
For i=myStart To myEnd
.Item(Rnd)=i
Next
For i=0 To .Count - 1
a(i)=.GetByIndex(i)
Next
End With
sh.Range("J2").Resize(UBound(a) + 1).Value=Application.Transpose(a)
End Sub

The procedure to produce a different colour each time is as follows.  It is linked to the above procedure which runs first.

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

ProduceUniqRandom 'Random Number generator
arr=Sheet1.Range("B2", Sheet1.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.

The following is the file which shows the workings.