jeudi 19 décembre 2019

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

first time posting for some help that I can't find after exhaustive search. I'm sure this is easy for someone who know what they are doing.

I'm looking for VBA that will cycle through background images (saved on my computer) within in a shape by clicking on the shape. I'm pasting 2 sets of code below that I found that gives me a good start but I can't figure out how to merge the code to get the result I'm looking for.

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

Hope that was easy to understand. Thanks in advance for the help!!!

Aucun commentaire:

Enregistrer un commentaire