Does This Make Sense To Anyone?

[jmanwell]

I have tried to figure this out but for the life of me I can't. First, I will show the 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.

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.

 

[jasonb75]

Try cycling through ClearPic using F8, would I be right in assuming that the error is kicking up on the cycle of the For - Next loop following deletion of the shape?

From what you have said now I think that is what's happening, I'm just not sure how it's affected by the change event.

 

[btadams]

Does the sheet in question have any protection?

 

[jmanwell]

No protection at all. I may not understand completely the cycling through using F8, but it didn't error out at all. I pressed F8 all the way through the code. It seemed to be fine. Would it normally bring up an error this way?

 

[jasonb75]

It should behave the same as running the macro normally, the only difference being that it runs line by line so you can monitor what's happening.

My last idea for tricking the code to working properly.

Public trick As Boolean
Sub ClearPic()
trick = True
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
trick = False
End Sub

Then as the first line of your Event code

If trick = True then Exit Sub

this should cancel the event macro if it's triggered by the ClearPic macro

 

[jmanwell]

Nice trick but as before, but still comes up with the same error.

 

[jasonb75]

I'm running out of ideas, I've copied the ClearPic macro and the change event to a test workbook, inserted a few pictures, and all seems to work fine. (Using the code from your original post, no modifications).

 

[jmanwell]

Did you insert a few pictures using the change event code or using the insert tab on the top of the screen? Also just as a matter of curiosity, what version of Excel are you running? If you inserted pictures using the insert tab at the top of the screen it works wonderfully.

 

[jasonb75]

I'm running 07 as well.

If you inserted pictures using the insert tab at the top of the screen it works wonderfully.

interesting, think I've been lookin at this from the wrong angle.

 

[jmanwell]

Actually, I should revise what I said. If you inserted a picture using the insert tab on a different sheet the code works wonderfully. If I insert a picture on the sheet where the change event is then it will still error out. I guess I always tried inserting a picture on a different sheet instead of the one that the change event is on.

 

[jasonb75]

I've just inserted some with the change event and then deleted with ClearPic no errors.

Tried single image, multiple images overlapped so all top left sorners in G2, multiple images spread out, insert 1 with event, 1 with ribbon tab, all working fine.