Name picture in code, How.

[Corey]

The below code inserts a picture into the active cell,
But i want ot name the picture a value in the cell above and 1 column to the
right of the cell where the picture is added.
This way i can then use another code to delete it by value.

Can some one assist me ?

Sub Picture_Adder()
Application.ScreenUpdating = False
Call WrkShtPUnP
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim myPic As Picture
Dim res As Variant
'Const sAddress As String = ActiveCell
Set WB = ActiveWorkbook
res = Application.GetOpenFilename _
("Image Files (*.jpg), *.jpg")
If res = False Then Exit Sub
Set SH = ActiveSheet
Set rng = ActiveCell
Set myPic = SH.Pictures.Insert(res)
With myPic
.Top = rng.Top
.Left = rng.Left
myPic.ShapeRange.LockAspectRatio = msoFalse
myPic.ShapeRange.Height = 175#
myPic.ShapeRange.Width = 235.5
myPic.ShapeRange.Rotation = 0#
'myPic.Name = Cells(-1, 1).Value ' <=== Get this to work ??
End With
Call WrkShtPPrt
Application.ScreenUpdating = True


Corey....

 

[Ken]

Corey

In most situations i believe that

myPic.Name =ActiveCell.Offset(-1, 1).Value

should work.

Good luck.
Ken
Norfolk, Va

 

[Dave Peterson]

You could use the Activecell like Ken suggested or you could use the picture's
..topleftcell.

..Name = .topleftcell.offset(-1, 1).Value

(Since you're in the "with mypic/end with" construct, you don't need to put
myPic on each of those lines, either.)

 

[Corey]

How about the deleting of the named picture:

Private Sub CommandButton1_Click()
Dim myPic As Picture
If TextBox1.Value = "" Then Exit Sub
If TextBox1.Value = myPic.Name Then ' <=== How do i get this to work ?
myPic.Select
With myPic
..Delete
End With
End If
End Sub
Corey...

 

[Dave Peterson]

I'd do something like:

on error resume next
activesheet.pictures(textbox1.value).delete
on error goto 0

or even check the error:

on error resume next
activesheet.pictures(textbox1.value).delete
if err.number <> 0 then
msgbox "not deleted, does it exist?
err.clear
else
msgbox "It's gone"
end if

 

[Corey]

thanks Dave.
Perfectly done.


Corey....

I'd do something like:

on error resume next
activesheet.pictures(textbox1.value).delete
on error goto 0

or even check the error:

on error resume next
activesheet.pictures(textbox1.value).delete
if err.number <> 0 then
msgbox "not deleted, does it exist?
err.clear
else
msgbox "It's gone"
end if