Does This Make Sense To Anyone?

jmanwell

New Member
Joined
Mar 18, 2010
Messages
33
I have tried to figure this out but for the life of me I can't. First, I will show the code:

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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Well, I don't feel so bad now; there have been many views and still no one can explain it to me.
 
Upvote 0
Well, I don't feel so bad now; there have been many views and still no one can explain it to me.

Choosing a descriptive topic title is the first step to take...
 
Upvote 0
Also it helps instead of us trying to figure out what your code is supposed to do, please explain, not in code, but in words what you are trying to accomplish exactly.
 
Upvote 0
I've tried running the code and got no errors. What other stuff is going on in the workbook?
 
Upvote 0
There is absolutely nothing else in the workbook that is going on. All the code that is in my first post is all the code that is in the workbook. The second code displays a picture based on the value of the Target cell. The value of the cell would be the name of the picture. I did a data validation of that cell to allow only the list of the picture names. Once selected the picture will be displayed in the Range of cells. The first code is a module that is suppose to delete the picture that was displayed. Instead all it does is give me an error. However, if I go to another sheet in the workbook, and from insert menu, insert a picture, place the top left corner of the picture in the Cell G2, and run the module, it will delete the picture. Both codes do work just fine except for the fact that the module will not run on the sheet that has the Worksheet_Change sub on it. I am trying to get this to work because I have another sheet that has two target cells that display two different pictures and may need to delete only one picture and not the other. The only way I know to distinguish between the two pictures would be by the topleftcell. But for whatever reason it doesn't seem to work with the Worksheet_Change sub. If i could get it to work on this sheet, I could probably get it to work on the other sheet as well.
 
Last edited:
Upvote 0
An uneducated guess would be that the ClearPic routine is triggering the event code, maybe disabling events while the ClearPic runs would solve the problem.
 
Upvote 0
Nice thought; I hadn't thought of that, but unfortunately, it doesn't work either. If I add On Error Resume Next to the ClearPic code it works except for the fact that it disables the data validation in the target cell. Is there anyway around that?

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.
 
Upvote 0
What I had in mind was more like

Rich (BB code):
Sub ClearPic()
Application.EnableEvents = False
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
Application.EnableEvents = True
End Sub

Not sure how or if that would work with what you have though.

I'm thinking that the event is creating a null filename in the line

Rich (BB code):
mPath = "C:\Documents and Settings\Blaine\My Documents\My Pictures\SH Wallpaper Contest\" & Target.Text & ".jpg"

returning a path of C:\Documents and Settings\Blaine\My Documents\My Pictures\SH Wallpaper Contest\.jpg

which is causing your runtime error.
 
Last edited:
Upvote 0
I tried it but doesn't work. Still get the "Application-defined or object-defined error." Once the error is displayed and I click the Debug button, it highlights the "If sh.TopLeftCell.Address = "$G$2"" statement. If I move the mouse over it, it shows a help box that says:

sh.TopLeftCell.Address = <application-defined or="" object-defined="" error="">Application-defined or object-defined error

But like I said, this same code works great on any other sheet when I insert a picture. Also, if I click back to the Excel sheet after selecting the Debug button, the picture has been deleted.
</application-defined>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top