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

Question

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:

Initial Shape: transparent background
Single Click on Shape: Transparent background replaced with BackgroundImage1
Another Single Click on Shape: BackgroundImage1 replaced with BackgroundImage2
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!!!

I’ve been working on this for awhile with no luck. I was hoping to hoping to get some help to get me going in the right direction but I guess it was a mistake to reach out for help on this site. I’ll keep digging elsewhere. — vutopia, Dec 20 '19 at 08:19

dreojs16 · Accepted Answer · 2019-12-20T13:35:46.780

0

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

edited Dec 20 '19 at 13:35

answered Dec 20 '19 at 12:42

dreojs16

109
12

You don't need the workbook.open. When you dim an integer it's set to zero. Alternatively you could declare a static variable within the procedure rather than a global – Harassed Dad Dec 20 '19 at 12:47
Yes, you are right. Stupid I did not see that... I will edit my answer. – dreojs16 Dec 20 '19 at 13:31
@HarassedDad Thank you so much! I had a little trouble initially because the shape was filling with white instead of transparent but I replaced: .Transparency = 1# and it seemed to fix it. You got me out of a jam! – vutopia Dec 20 '19 at 15:48

Hoàng Thanh Tùng · Answer 2 · 2023-04-01T16:55:53.323

you Can use code : Remove Background image/ Picture as use tool picture format -> set transparent color in excel .

 Sub RemoveBackground()
   Dim selectedPicture As Picture
    Set selectedPicture = ActiveSheet.Pictures("Picture 3")
 ' Set the transparent color of the picture
    With selectedPicture.ShapeRange.PictureFormat
        .TransparentBackground = True
        .TransparencyColor = RGB(255, 255, 255)
    End With 
End Sub

=================

    Sub RemoveShapes()
    ' Select the image you want to remove the background from
    Dim selectedImage As Shape
    Set selectedImage = ActiveSheet.Shapes("Image1")     ' Set the transparent color of the image 

    With selectedImage.PictureFormat
        .TransparentBackground = msoTrue
        .TransparencyColor = RGB(255, 255, 255)
    End With
  End Sub

Thank u

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

2 Answers2