Excel VBA to change background image of shape by clicking on shape

Question

First time posting for assistance that, despite my best efforts, I can't find. For someone who knows what they are doing, this must be simple.

I'm seeking for VBA that will let me click on a shape and have it cycle between backdrop images that I've saved on my PC. I've pasted two sets of code below that I found to be a decent place to start, but I'm not sure how to combine them to produce the outcome I want.

Ultimately, I want each click on a shape to keep cycling through the following command:

1. Initial Shape: transparent background
2. Single Click on Shape: Transparent background replaced with BackgroundImage1
3. Another Single Click on Shape: BackgroundImage1 replaced with BackgroundImage2
4. Another Single Click on Shape: BackgroundImage2 replaced with transparent background

I've found this code that works well to change color of the shape by clicking:

Sub trafficlight()
Dim WhoAmI As String, sh As Shape
    WhoAmI = Application.Caller
    With ActiveSheet.Shapes(WhoAmI).Fill.ForeColor
        Select Case .RGB
            Case vbRed
                .RGB = vbGreen
            Case vbGreen
                .RGB = vbYellow
            Case Else
                .RGB = vbRed
        End Select
    End With
End Sub

And then this code to change the shape with a image saved on my computer:

Sub Rectangle9_Click()
Dim WhoAmI As String, sh As Shape
    WhoAmI = Application.Caller
    With ActiveSheet.Shapes(WhoAmI).Fill
        .Visible = msoTrue
        .UserPicture "C:\Users\username\Desktop\BackgroundImage1.png"
        .TextureTile = msoFalse
    End With
End Sub

Can someone please help me with this?

Answer 1

You need to keep track of what image is currently displayed. You could set an integer for each time the image changes.

Option Explicit

Sub ChangeShapePic()
Static i As Integer

With ActiveSheet.Shapes(Application.Caller).Fill
    Select Case i
        Case 0
            .UserPicture ("C:\Users\username\Desktop\BackgroundImage1.png")
            i = 1
        Case 1
            .UserPicture ("C:\Users\username\Desktop\BackgroundImage2.png")
            i = 2
        Case 2
            .UserPicture ("C:\Users\username\Desktop\BackgroundImage3.png")
            i = 3
        Case 3
           .Solid
           .Transparency = 0#
            i = 0
    End Select
End With
End Sub