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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jmanwell

New Member
Joined
Mar 18, 2010
Messages
33
Well, I don't feel so bad now; there have been many views and still no one can explain it to me.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,964
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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...
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,942
I've tried running the code and got no errors. What other stuff is going on in the workbook?
 

jmanwell

New Member
Joined
Mar 18, 2010
Messages
33
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,831
Office Version
  1. 365
Platform
  1. Windows
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.
 

jmanwell

New Member
Joined
Mar 18, 2010
Messages
33
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,831
Office Version
  1. 365
Platform
  1. Windows
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:

jmanwell

New Member
Joined
Mar 18, 2010
Messages
33
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:

Forum statistics

Threads
1,171,991
Messages
5,878,644
Members
433,355
Latest member
Excelguru87

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
Top