0

What I have done is created a macro that deletes any images in a certain range of cells if an image is present Range("D199:U205" and then copies an image named "JB Sig" at the bottom of the sheet, and pastes it to the now blank location in the sheet.

I am getting "Run-time error '1004': Application-defined or object-defined error" when I use the macro. When this error occurs and I debug, the line that is causing problems is If Not Intersect(Range("D199:U205"), Range(Pic.TopLeftCell, Pic.BottomRightCell)) Is Nothing Then Pic.Delete

I have rewritten this in numerous ways given examples online but the error is still causing me problems.The weird thing is, sometimes I get the error, other times I don't, and even a minimal cosmetic change in the VBA code can fix it (temporarily). So at the moment the macro is working perfectly on my computer. However, my colleague who is accessing the same file (in a different office) is still getting the run-time error.

I am very inexperienced with code and any VBA I write is usually copied and pasted from somewhere, as opposed to me understanding what I am doing. So please be patient with my stupidity. Thanks

I've tried unprotecting the VBAProject by getting rid of a password I had to view it. This didn't work. I've tried changing the code for deleting the images to various different suggestions online but I am still running into the same error at times. There appears to be many different ways of getting the code to work but they are all susceptible to run-time errors.

Sub Approval_JB()
'
' Approval_JB Macro
'

'
    Dim ws As Worksheet
    Set ws = Sheets("Issue Letters")
    ws.Unprotect Password:="john123"
    Dim Pic As Object
    For Each Pic In ws.Shapes
    Dim R As Range
    Debug.Print Pic.Name
    Debug.Print Pic.TopLeftCell.Address
    Debug.Print Pic.BottomRightCell.Address
    Set R = Range(Pic.TopLeftCell, Pic.BottomRightCell)
    If Not Intersect(Range("D199:U205"), R) Is Nothing Then Pic.Delete
    Next Pic
    Range("D199:U205").ClearContents
    ws.Shapes.Range(Array("JB Sig")).Select
    Selection.Copy
    ws.Range("D201").Select
    With ws.Range("D199")
    .Value = "Yours Faithfully"
    End With
    With ws.Range("D204")
    .Value = "Joe Bloggs"
    End With
    With ws.Range("D205")
    .Value = "Engineer"
    End With
    Range("B192").Select
    ws.Protect Password:="john123"
End Sub

It works sometimes, as described above. However other times when I run the macro, this error appears.

Run-time error '1004': Application-defined or object-defined error

John O'Dowd
  • 1
  • 2
  • 1
    Im noticing that your using FormulaR1C1, And you don't put a formula in it. You should probably use `.Value` also using `.Select` is considered bad practice. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – erazorv4 Jun 18 '19 at 11:58
  • One debugging idea is to declare a range variable, `R`, and split the problem line into two lines: `Set R = Range(Pic.TopLeftCell, Pic.BottomRightCell)` followed by `If Not Intersect(Range("D199:U205"), R) Is Nothing Then Pic.Delete`. This won't fix the problem, but should help in focusing your debugging efforts. – John Coleman Jun 18 '19 at 12:10
  • The "bad practice" etc. doesn't even slightly surprise me, I have no idea what I am doing. Could I be cheeky and ask how you would rewrite the above code if you were doing it? I think I know how to use the .value function given your useful link. However, I'm not sure how to copy and paste the image without using .Select. Any help appreciated. – John O'Dowd Jun 18 '19 at 12:59
  • I would suggest you use explicit references to the worksheet where the shape is located instead of `Activesheet`. – Stavros Jon Jun 18 '19 at 13:20
  • Thanks, using explicit references will hopefully make a difference – John O'Dowd Jun 18 '19 at 14:03
  • The shape-deleting loop seems unproblematic in and of itself. I don't know how to make it fail. I wonder a bit about `ActiveSheet.Unprotect Password:="john123"`. Perhaps there is some permission/protection problem that pops up sometimes but not others. – John Coleman Jun 18 '19 at 14:25
  • I've totally rewritten code with your suggestions guys, but still running into the same issue unfortunately. – John O'Dowd Jun 18 '19 at 15:25
  • Without a [mcve] it is hard to say anything. If you did split the code into two lines like I suggested earlier, which of the resulting lines is throwing the error? If the former (`Set R = Range(Pic.TopLeftCell, Pic.BottomRightCell)`) perhaps put the three lines `Debug.Print Pic.Name` , `Debug.Print Pic.TopLeftCell.Address` and `Debug.Print Pic.BottomRightCell.Address` before that. See if any of those lines fail (and look at what is printed before the crash). – John Coleman Jun 18 '19 at 15:36
  • Thanks @JohnColeman. I have edited the main code above to display what I now have. I did split the code like you suggested and it was the former line {Set R = Range(Pic.TopLeftCell, Pic.BottomRightCell)} that was throwing an error. When I put in the additional debug lines, the line {Debug.Print Pic.TopLeftCell.Address} failed, as did the next one when I deleted it. What is printed in the immediate window doesn't mean anything to me, I'm just not sure what I'm supposed to be looking at. Any ideas? – – John O'Dowd Jun 19 '19 at 08:45
  • The last thing printed should be the name of the shape which triggers the error. That might be informative. What version of Excel are you using? [This blog post](https://excelinyouroffice.blogspot.com/2014/09/vba-remove-all-shapes-in-sheet.html) has the cryptic remark (about some similar code) "in some versions of Excel, above code was said to remove the comments and AutoFilter as well". I also have a hunch that Excel might be trying to delete a shape which has already been deleted, but can't replicate it. – John Coleman Jun 19 '19 at 11:37
  • Hi again John. Thanks so much for your help. I think I got it to work by using a workaround. It was something to do with the image already being on the page. Instead I've written it to insert the image as a picture, changing the file from png to jpeg. It has brought additional problems in terms of having to write a code for transparency afterwards but I think it is working now. Thanks again for your patience as I don't have any clue what I am doing! – John O'Dowd Jun 19 '19 at 14:16

1 Answers1

0

Only error 1004 doesn't imply to some specific error, but my guess is that the error comes in this case because of the Range(Pic.TopLeftCell, Pic.BottomRightCell).

Try this: Range(Pic.TopLeftCell.Address & ":" & Pic.BottomRightCell.Address)

MMakela
  • 167
  • 2
  • 16
  • 2
    "my guess" suggests that this should be a comment rather than an *answer*. – John Coleman Jun 18 '19 at 12:01
  • `Range(Pic.TopLeftCell, Pic.BottomRightCell)` in itself works perfectly. No need to convert a Range to a string (via `Address`-method) and let VBA convert this back. – FunThomas Jun 18 '19 at 12:18
  • @JohnColeman And how come this answer isn't a proper solution for a question? – MMakela Jun 18 '19 at 12:19
  • You had the words "guess" and "try". A proper solution should diagnose the problem and fix the specific diagnosis with tested code. You gave a suggestion, one which (as @FunThomas points out) won't make a difference. – John Coleman Jun 18 '19 at 12:30
  • Thank you for your contributions gentlemen. So, FunThomas am I correct in saying you don't think this actually is a solution? It is difficult for me to tell whether anything works right away, as often solutions work for a while before I start getting the 1004 error again :( – John O'Dowd Jun 18 '19 at 12:53