Excel vba automation: Assign macro to imported graphic

I assign a macro to a graphic that I have imported via xl_Ribbon/Insert/Picture :
    ActiveSheet.Shapes.Range(Array("Picture 3")).Select
    Selection.OnAction = "tst"

BUT, if I import a graphic to an Excel cell, how do I identify that graphic so I can use vba to assign a macro to it?

With Range(Cells(rn.row + 1, 7), Cells(rn.row + 1, 1))
   ActiveSheet.Shapes.AddPicture "path\x.jpg", msoCTrue, msoTrue, .left, .top, -1, -1
   ActiveSheet.Shapes(ActiveSheet.Shapes.Count).height = .height
End With

If you could briefly explain the principle of connecting input file name with
Shapes.Range, it would help me!

Thanks in anticipation
Kelvin
Kelvin4Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NorieConnect With a Mentor VBA ExpertCommented:
The code I posted would assign a macro to the most recently added shape, it would go straight after the code you posted.

If you had something like this sh would be a reference to the newly added picture.
Dim sh As Shape
set sh = ActiveSheet.Shapes.AddPicture "path\x.jpg", msoCTrue, msoTrue, .left, .top, -1, -1

Open in new window

With that reference you could assign a macro like this.
sh.OnAction = "TheMacroToAssign"

Open in new window

0
 
NorieVBA ExpertCommented:
Can't you just use this?
 ActiveSheet.Shapes(ActiveSheet.Shapes.Count).OnAction = "tst"

Open in new window

0
 
Kelvin4Author Commented:
Not sure!
The sheet will contain potentially hundreds of rows with graphic icons, but each icon is added at a separate time, as new data rows are appended. I only need to assign the macro (once) to each new data row.

Also, what If I dont want that macro assigned to certain shapes on the ActiveSheet?
Thanks..
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Kelvin4Author Commented:
would I  be right in thinking that if I could:
Dim sh As Shape
set sh = ActiveSheet.Shapes.AddPicture "path\x.jpg", msoCTrue, msoTrue, .left, .top, -1, -1

then sh.name would identify the shape for the assignment of the macro?

Kelvin
0
 
Kelvin4Author Commented:
The text & code you wrote:
"Can't you just use this?
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).OnAction = "tst"     "

Kelvin then proposed what you claim to be the correct answer:
set sh = ActiveSheet.Shapes.AddPicture "path\x.jpg", msoCTrue, msoTrue, .left, .top, -1, -1

then sh.name would identify the shape for the assignment of the macro?

You then write
"The code I posted would assign a macro to the most recently added shape, it would go straight after the code you posted. "

I dont think so, it would have assigned the macro to all shapes on active page!!

But thanks for the contribution.
Kelvin
0
 
NorieVBA ExpertCommented:
Kelvin

Not sure what you mean.

The first code I posted would only assign the macro to the most recent picture added.

This returns one shape/picture - the last member of the Shapes collection.
  ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

Open in new window

0
 
Kelvin4Author Commented:
ok, thanks
0
All Courses

From novice to tech pro — start learning today.