Change Button Colour with VBA

Adding a random colour to a toggle button is a bit of a novelty in an Excel workbook.  You could run a procedure after another procedure to change the appearance of a shape.   This tutorial will show you how.

The following will add a different colour each time you click on the shape within the sheet.  Below is an example of the shapes in the attached workbook.  Each click will produce a different colour except in circumstances when the random number generator lands on the same number by chance. 

By clicking on any of these shapes the colour will change.  The VBA code which generates this procedure is as follows;

Option Explicit
Sub ToggleColors()
Dim sShape As Shape
Dim pica As Integer 'Pica=pick a colour

pica = Int(10 * Rnd + 1)
Set sShape=ActiveSheet.Shapes(Application.Caller)

With sShape.Fill
If .Visible=True Then
.Visible=False
Else
.Visible=True
.ForeColor.SchemeColor=pica
End If
End With
End Sub

As with so many things in Excel there are many ways to do the same thing.  The following draws on the learnings from the Heat Map in Excel.  This method allows you to toggle the colours of a shape in Excel.  The VBA procedure does this by changing the value in F9 from 1 to 2.  The following is the VBA to achieve the task.

Option Explicit

Sub ColourShp() 'Excel VBA procedure to colour a shape.
Dim shp As Object

Set shp=Sheet1.Shapes("Rectangle 1")

If [F9] = 1 Then
[F9] = 2
shp.Fill.Visible=msoTrue
shp.Fill.ForeColor.RGB=RGB(255, 0, 0)
Else
[F9]=1
shp.Fill.Visible=msoFalse
End If
End Sub

The attached file shows the workings of the above VBA procedure.