asked on

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

SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jacques Geday

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

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger

ASKER

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