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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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,953
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,955
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,936

ADVERTISEMENT

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
10,874
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
10,874
Office Version
  1. 2019
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,393
Messages
5,528,466
Members
409,818
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top