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.

 

[jmanwell]

That is completely messed up. Why would there be a difference on which computer the code was ran on as too whether or not it works? It's nice to know my code is correct though, but still upsetting that it doesn't work for me.

 

[jasonb75]

Also just a thought. Would it matter that I'm using Excel 2007? Seems to me they did some reworking of the shapes and objects for Excel 2007. I have updated to service pack 2 though.

Thats the only thing I can think of that might cause the error, I had a hard drive failure a couple of months back and don't think I've updated excel since I reinstalled it.

 

[jmanwell]

I am completely confused. I use Excel 2007 SP2 at work, but I have Excel 2007 with no SP installed on my personal computer. The code won't work on either one of the computers. Maybe I need SP1. I checked the version by going to the Excel options and clicking the Resources option on the left then clicking the About button. The version appears at the top of the window that opens. I would have thought that by SP2 they would have gotten these glitches fixed.

 

[jasonb75]

Something that I just thought of after deleting the test file.

Do the images you're inserting have read only protection in the source folder? I don't think that would carry into excel but don't know for sure.

 

[wigi]

No problem for me, Excel 2007 SP2.

Condensed code:

Sub ClearPic()
    For Each sh In ActiveSheet.Shapes
        If sh.TopLeftCell.Address = "$G$2" Then sh.Delete
    Next
End Sub

and

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Errorcatch
    If Target.Address = "$C$4" Then
        mPath = "C:\Documents and Settings\Blaine\My Documents\My Pictures\SH Wallpaper Contest\" & Target.Text & ".jpg"
        If Len(Dir(mPath)) Then
            With ActiveSheet.Pictures.Insert(mPath)
                .ShapeRange.Height = 223.5
                .ShapeRange.Width = 191.25
                .Top = [G2].Top: .Left = [G2].Left
            End With
        End If
    End If
    Exit Sub
Errorcatch: MsgBox Err.Description
End Sub

 

[jasonb75]

If all else fails, copy the code to a fresh workbook and see if it works for you there, if it does then copy your existing workbook content to the new one.

As Wigi and I have both been able to use your code without problems it could possibly be some sort of file corruption.

 

[jmanwell]

I was thinking the same thing about copying the code to a new workbook; however, it still gives me the same error about application-defined or object-defined. I think it must be some glitch or something. I have tried it on two different computers with two different installs of Excel 2007 one patched to SP2, the other not patched at all. Neither of them works for me. I have been dealing with VBA for only a month or two and it is nice to know that the code I came up with (albeit not as condensed as it could have been) still works, but it is extremely frustrating when it doesn't work for me.

 

[wigi]

I was thinking the same thing about copying the code to a new workbook; however, it still gives me the same error about application-defined or object-defined. I think it must be some glitch or something. I have tried it on two different computers with two different installs of Excel 2007 one patched to SP2, the other not patched at all. Neither of them works for me. I have been dealing with VBA for only a month or two and it is nice to know that the code I came up with (albeit not as condensed as it could have been) still works, but it is extremely frustrating when it doesn't work for me.

You're right and given the 2 monts, you're doing pretty fine.

That being said, there's not much helpers can do if they can't reproduce the problem.

Good luck,

Wigi