VBA Error "Run-Time error'1004': PrintOut Method of Worksheet class failed

[ranie]

Hello all,
This is the VBA code that I have to loop the dropdown list and print. I am getting an error message when I click cancel during the loop. How do I disable or not show the error message?
VBA Error "Run-Time error'1004': PrintOut Method of Worksheet class failed
It asks me to debug ActiveSheet.Printout line

Sub Test()

Dim R As Range
For Each R In Sheets("PNL").Range("s9:s32")
If Not IsEmpty(R) Then
Range("B1:C1") = R
ActiveSheet.PrintOut

End If
Next
End Sub

Thank you very much

 

[Joe4]

Sub Test()

Dim R As Range
For Each R In Sheets("PNL").Range("s9:s32")
If Not IsEmpty(R) Then
Range("B1:C1") = R
ActiveSheet.PrintOut

End If
Next
End Sub

You have a few options. You can add error handling code to handle that kind of error (do a Google Search on "Excel VBA error handling" or you can simply ignore it, like this:

Dim R As Range
For Each R In Sheets("PNL").Range("s9:s32")
    If Not IsEmpty(R) Then
        Range("B1:C1") = R
        On Error Resume Next
        ActiveSheet.PrintOut
        On Error GoTo 0
    End If
Next

End Sub

 

[ranie]

Dim R As Range For Each R In Sheets("PNL").Range("s9:s32") If Not IsEmpty(R) Then Range("B1:C1") = R On Error Resume Next ActiveSheet.PrintOut On Error GoTo 0 End If Next End Sub

Thanks, but I will continue the loop. is there a way to stop the loop in the middle on cancel?

 

[Joe4]

OK, then we should probably use error handler there, i.e.

Dim R As Range

On Error GoTo err_chk
For Each R In Sheets("PNL").Range("s9:s32")
    If Not IsEmpty(R) Then
        Range("B1:C1") = R
        ActiveSheet.PrintOut
    End If
Next
On Error GoTo 0

Exit Sub

err_chk:
    If Err.Number = 1004 Then
        MsgBox "Printing has been cancelled"
        Exit Sub
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[ranie]

Dim R As Range On Error GoTo err_chk For Each R In Sheets("PNL").Range("s9:s32") If Not IsEmpty(R) Then Range("B1:C1") = R ActiveSheet.PrintOut End If Next On Error GoTo 0 Exit Sub err_chk: If Err.Number = 1004 Then MsgBox "Printing has been cancelled" Exit Sub Else MsgBox Err.Number & ":" & Err.Description End If End Sub

This is perfect!!!!!! thanks!

 

[Joe4]

You are welcome.