I have tried to figure this out but for the life of me I can't. First, I will show the code:
This works great just running the macro after inserting a picture from the insert menu and placing the top left corner in Cell G2. However, if I try to run this macro on a sheet with the following code, I get a Runtime Error 1004 Application-defined or Object-defined Error.
Does this make sense to anyone? If so please explain it to me because I would love to know.
Code:
Sub ClearPic()
Dim ws As Worksheet
Dim sh As Shape
Set ws = ActiveSheet
For Each sh In ws.Shapes
If sh.TopLeftCell.Address = "$G$2" Then sh.Delete
Next sh
End Sub
This works great just running the macro after inserting a picture from the insert menu and placing the top left corner in Cell G2. However, if I try to run this macro on a sheet with the following code, I get a Runtime Error 1004 Application-defined or Object-defined Error.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim mPath As String
Dim sh As Shape
On Error GoTo Errorcatch
If Not Application.Intersect(Range("C4"), Target) Is Nothing Then
mPath = "C:\Documents and Settings\Blaine\My Documents\My Pictures\SH Wallpaper Contest\" & Target.Text & ".jpg"
If Dir(mPath) <> "" Then
Set TargetCells = Range("G2")
Set ws = ActiveSheet
Set p = ws.Pictures.Insert(mPath)
With TargetCells
t = .Top
l = .Left
End With
With p
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 223.5
.ShapeRange.Width = 191.25
.Top = t
.Left = l
End With
End If
End If
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub
Does this make sense to anyone? If so please explain it to me because I would love to know.