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

0 votes

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?

Sep 22, 2022 in Others by Kithuzzz
• 38,020 points
2,698 views

1 answer to this question.

0 votes

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
answered Sep 23, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
0 answers

Excel VBA: How to change order of scenarios

I'm trying to run a VBA loop ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 38,020 points
333 views
0 votes
1 answer

How to change the background color of AppBar in Flutter?

Hi@akhtar, You can add backgroundColor keyword in your ...READ MORE

answered Aug 12, 2020 in Others by MD
• 95,460 points
2,021 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,539 views
0 votes
1 answer

How to show caller image on iOS native ui call by using callkit framework?

Apple's CallKit is a framework introduced with iOS ...READ MORE

answered Nov 4, 2022 in Others by gaurav
• 23,260 points
1,274 views
0 votes
1 answer

Shape Names In Excel

Try this: Option Explicit Sub Button1_Click() ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,720 points
1,075 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,088 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,504 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
807 views
0 votes
1 answer

VBA Excel: Write timestamp to cell on change of another cell

Your request is a little vague, and ...READ MORE

answered Feb 5, 2023 in Others by narikkadan
• 63,720 points
945 views
0 votes
1 answer

How To Copy/Cut Row of Data Based on TRUE/FALSE Condition [Excel VBA]

Solution Loop through the rows on the Price ...READ MORE

answered Feb 4, 2023 in Others by narikkadan
• 63,720 points
871 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP