One way to implement persistence across VBA resetting global variables
is with your own names. Something like the code below, which names the
inserted object with a unique custom name. If you want to save the
information in a cell, save the object's name, not the name of the file
used to create the object.

Option Explicit
Sub addAPic(whatPic As String)
Dim x As Picture
On Error Resume Next
ActiveSheet.Shapes("_TMPic").Delete
On Error GoTo 0
Set x = ActiveSheet.Pictures.Insert(whatPic)
x.Name = "_TMPic"
End Sub
Sub Add1()
addAPic "C:\pic1.jpg"
End Sub
Sub Add2()
addAPic "C:\pic2.jpg"
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , Richard1284
says...
Hi sorry but that last thread, here is my code I want to display a
different pic as each button s clicked however as I click the button I
also want to delete the old one. I am having problems with the latter.
I haave outlined where there is an error in my code, can you please
check it out and give me some ideas on what to do?
Much appreciated


Public myPic As Picture '
Public sPic As String
Sub InsertPic1()
'
' InsertPic1 Macro
' Macro recorded 23/05/2004 by Richard


sPic = Sheets("Main").Cells(100, 6).Value
------------------------------------------------------------
'error here on trying to detect if there is already a pic on sheet

If sPic < "" Then
'I get the error here
Set myPic = ActiveSheet.Pictures(sPic)
myPic.Delete
End If
--------------------------------------------------------------

sPic = ("F:\Dss\ass\Arran1.jpg")
Sheets("Main").Select
Range("D16").Select
Set myPic = ActiveSheet.Pictures.Insert(sPic)

'storing pic ref here for delete
Sheets("Main").Cells(100, 6).Value = sPic

End Sub


---
Message posted from http://www.ExcelForum.com/