Excel .AddPicture to inactive worksheet

To add picture 'myPic' (and onAction property) to Active worksheet("Item_Files"): I write this:

    Set wsIF = ThisWorkbook.Worksheets("Item_Files")
    With Range(Cells(3, 1), Cells(3, 1))
        ActiveSheet.Shapes.AddPicture "D:\path\myPic.jpg", msoCTrue, msoTrue, .Left, .Top, -1, -1
        ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Height = .Height
        ActiveSheet.Shapes(ActiveSheet.Shapes.Count).OnAction = "item_Files_shapeButtons"
    End With

- But, several pics are to be added at same time, and worksheet("Item_Files") is INACTIVE
------------

After a struggle, I found the code below added picture 'myPic' (and onAction property) to INactive worksheet("Item_Files"):
   
    Set wsIF = ThisWorkbook.Worksheets("Item_Files")
    With wsIF.Range("X1")
        wsIF.Shapes.AddPicture "D:\path\myPic.jpg", msoCTrue, msoTrue, .Left + 1, .Top + 1, -1, -1    
        wsIF.Shapes(wsIF.Shapes.Count).Height = .Height
        wsIF.Shapes(wsIF.Shapes.Count).OnAction = "item_Files_shapeButtons"
    End With

- One or more pics were delivered to the right sheet and cell, but to display the pics I had to Activate wsIF (ie  worksheet("Item_Files")), by clicking its tab.

Since clicking the tab is essential to see the result, I dont object to that (I only mention it in case you see signs of a flaw in my code)
But this is my first experience of adding pictures, and my next task is the add and remove some dynamically.

Each time I add a pic, I need to add  wsIF.Shapes(wsIF.Shapes.Count).NAME to a lookup list on another sheet.
So I need to repeatedly alternate between sheets.

THEREFORE I ask: is my code optimal - and what should I take on board for this exercise?

Many thanks
Kelvin4
Kelvin4Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
I would suggest that since Addpicture returns a reference to the shape added, you use it rather than relying on shapes.count:

Dim shp as Shape
 Set wsIF = ThisWorkbook.Worksheets("Item_Files")
    With wsIF.Range("X1")
        Set shp = wsIF.Shapes.AddPicture("D:\path\myPic.jpg", msoCTrue, msoTrue, .Left + 1, .Top + 1, -1, -1)
        shp.Height = .Height
        shp.OnAction = "item_Files_shapeButtons"
    End With

Open in new window


Note: you do not need to switch back and forth between sheets (in the sense of selecting/activating them) in order to add the names to a list or to add the shapes.
0
 
gowflowConnect With a Mentor Commented:
As I see your code is fine and actually you thought me how to add a picture which I never did so far in VBA !!! :)
Now if you need more help to loop or anything pls let me know and much easier if you post a sample workbook we can then take it from there.

gowflow
0
 
gowflowCommented:
BTW what is the macro item_Files_shapeButtons consist of could you post the code ?

And what do you mean by:

 added picture 'myPic' (and onAction property) to INactive worksheet("Item_Files"):

gowflow
0
 
Kelvin4Author Commented:
Hi goflow and Rory,
Your rapid comments were much appreciated, and you were right, not much needed to be changed.
But the point was I NEEDED some encouragement, and CHECKING that I was not going off crazily. And you offered it..
Hence points to both.

Set sh = wsIF.Shapes.AddPicture("D:Path|myPic.jpg"...    worked well

The only oddity was with the first code line (below). In my trials that line always read: With wsIF.range(Cells(3, i ) , Cells(3, i)) . But I built into the loop that drops successive graphics, another layer of conditions to control if a graphic was to be dropped (using conditions from data on a different ws). With that extra complication, the range statement wsIF.range(Cells(3, i ) , Cells(3, i))  blew up.Specifically: it would no longer work on an inactive sheet.

So! I guessed on: With wsIF.Cells(3, i ) and it worked on active and inactive sheets, though is that really a RANGE statement?
Maybe the With statement does not need to be a RANGE statement?!

                With wsIF.Cells(3, i + 1)
                    Debug.Print "Hi cells_cl = "; i + 1
                    Set sh = wsIF.Shapes.AddPicture("D:\Path\"myPic.jpg", msoCTrue, msoTrue, .Left, .Top + 1, -1, -1)
                    wsIF.Shapes(wsIF.Shapes.Count).Height = .Height
                    wsIF.Shapes(wsIF.Shapes.Count).OnAction = "Item_Files_Icons_onAction"
                End With

Its difficult to pass over a live file, as there is a graphics folder, and other external stuff.  But I'll return more details if its of any help in return. Now the piece works dynamically: individual graphics can be switched in and out of the sheet depending on conditions.

Kelvin
0
All Courses

From novice to tech pro — start learning today.