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 in Others by Kithuzzz
• 12,240 points
26 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 by narikkadan
• 20,880 points

Related Questions In Others

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,360 points
1,357 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 in Others by gaurav
• 18,960 points
337 views
0 votes
0 answers

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

In my iOS app, I am using ...READ MORE

Sep 22 in Others by gaurav
• 18,960 points
21 views
0 votes
0 answers

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

In my iOS app, I am using ...READ MORE

Sep 23 in Others by gaurav
• 18,960 points
11 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,720 points
402 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
2,095 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 in Others by gaurav
• 18,960 points
41 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
134 views
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

Heres a function. Can't remember where I ...READ MORE

answered 6 days ago in Others by narikkadan
• 20,880 points
26 views
0 votes
1 answer
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