Error Handling Stuck in a Loop

[zalmyc]

I was hoping somebody could help me with this. I've set up a code in Excel VBA to copy from Excel and paste into word (courtesy of vbaexpress). I'm trying to create a simple error handler in which errors are first routed to the error handler code, and a message box pops up saying that there are errors. This works fine. However, I also want the user to be able to choose to continue anyway - for example, if the user chooses Yes to the message box, it changes the error handling in the code to On Error Resume ContinueAnyway (sort of like Resume Next, but to resume back at the top of the code). The problem is it puts it into a loop. When the error is present, the message box pops up. When I click Yes, it pops up again, etc. Here's the code. I'm using Excel 2010 on Windows 7. Any ideas what I'm doing wrong or how to make it work? Thanks!


[VBA]Sub PasteToWord()
'set a reference to the Word Object library from your Excel spreadsheet
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")

On Error GoTo ErrorHandler

ProceedAnyway:
Sheets("Sheet0").Range("A1:A3").Copy
With objWord
.Documents.Open ("C:\Users\Zalmy\Desktop\PasteDoc.docx")
.ActiveDocument.Bookmarks("FirstBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With

Application.CutCopyMode = False

Sheets("Sheet1").Range("D2:E4").Copy
With objWord
.ActiveDocument.Bookmarks("SecondBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With

Application.CutCopyMode = False

Sheets("Sheet1").Range("K2:L4").Copy
With objWord
.ActiveDocument.Bookmarks("ThirdBookmark").Select
.Selection.PasteAndFormat (wdChartPicture)
.Visible = True
End With

Application.CutCopyMode = False

Exit Sub

ErrorHandler:
Application.CutCopyMode = False
If MsgBox("A copy/paste error task was encountered. Continue anyway?", vbYesNo) = vbYes Then
Resume ProceedAnyway
End If

End Sub[/VBA]

[mikerickson]

zalmyc,
Welcome to the forum.
Please take a moment to read the rules.
Particularly the rule about wrapping your code.

I've done it for you this time.


About your problem:
Your error handler returns execution to before the error occurred, but without changing the condition that caused the error.

That's why it loops.

Return to a point after the error.

P.S. its also possible that something other than the copy/paste could cause an error. I would test the value of Err to see what caused the error.

[zalmyc]

Thanks mikrickson. What you're saying makes sense but the solution would require me to insert a new vba GoTo tag after each line in the code that could potentially generate an error. The above code is actually a snippet of a much larger code, so inserting a new tag would be cumbersome (although I'd do it if that was the only solution). However, I was wondering if there is some way to have the code change from On Error GoTo Error Handler to On Error Resume Next after the first run. I have tried inserting On Error Resume Next within my error handler, but this doesn't work. It seems like there should be a way to do this. Anyway, thanks for your help!

[mikerickson]

What line(s) would throw the error you are trying to trap?

[mikerickson]

I think this should work

[VBA]Sub PasteToWord()
'set a reference to the Word Object library from your Excel spreadsheet
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")

On Error Resume Next

Sheets("Sheet0").Range("A1:A3").Copy
With objWord
.Documents.Open ("C:\Users\Zalmy\Desktop\PasteDoc.docx")
.ActiveDocument.Bookmarks("FirstBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With

If Err Then GoSub AskQuestion
Err.Clear

Application.CutCopyMode = False
Sheets("Sheet1").Range("D2:E4").Copy
With objWord
.ActiveDocument.Bookmarks("SecondBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With

If Err Then GoSub AskQuestion
Err.Clear

Application.CutCopyMode = False
Sheets("Sheet1").Range("K2:L4").Copy
With objWord
.ActiveDocument.Bookmarks("ThirdBookmark").Select
.Selection.PasteAndFormat (wdChartPicture)
.Visible = True
End With

On Error GoTo 0
Application.CutCopyMode = False
Exit Sub


AskQuestion:
If MsgBox("Continue", vbYesNo) = vbYes Then Return
End Sub[/VBA]

[zalmyc]

Yes, this definitely works. I have to tweak it a bit to make sure it doesn't paste things in the wrong place, but this is exactly what I was looking for. Thanks!