In Event printer fails

[Graham]

I have a procedure detailed below which was created, thanks to support from
this group, to remove highlighted areas from worksheets before printing an
area and then after printing to return the sheet to the areas highlighted as
before. This works fine but there is a problem which can occur in two
situations. If the procedure is activated (from a button ) when a printer is
not connected or if a printer error occurs during printing (which does
happen on some network printers), then the sheet is left with the areas
without the highlights. ie the line to restore the highlights is after the
print command as you will see. Is there a way to error trap for this type of
situation? I as always value any help or guidance.

Sub printmargins()
Dim myRng As Range
Dim myColorIndex As Long
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Range("C2").Value > 0 Then
Set myRng = Nothing
On Error Resume Next
Set myRng = sh.Range(sh.Name)
On Error GoTo 0
If myRng Is Nothing Then
'do nothing, it didn't have a range with the worksheet name
Else
myColorIndex = myRng(1).Interior.ColorIndex
myRng.Interior.ColorIndex = xlNone
With sh.PageSetup
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0.551181102362205)
.RightMargin = Application.InchesToPoints(0.15748031496063)
End With
sh.Range("PRGM").PrintOut
myRng.Interior.ColorIndex = myColorIndex
End If
End If
Next sh
End Sub

Kind regards,
Graham Haughs
Turriff, Scotland

 

[Guest]

In the code you use earlier in the procedure you can find the answer to your question. The 'On Error Resume Next' statement says to just skip over any errors. So when it tries to assign the Sheet name to a name that may not exist, it would just skip that error. Then the programmer who wrote that code had errors turned back on with the statement 'On Error GoTo 0'. So you can precede and proceed any code you expect to have an error with those statements

For example
On Error Resume Nex
sh.Range("PRGM").PrintOu
On Error GoTo

You might want to evaluate the error code however, so you can notify your user that there was a printing problem. But since we ignored errors earlier in the procedure, we need to clear (the Err object's Clear method) the Err, so we can evaluate properly. Consider these changes

On Error Resume Nex
Err.Clea
sh.Range("PRGM").PrintOu
If Err.Number <> 0 Then msgbox "There was an error printing.
On Error GoTo

I hope that helps

-Bra
----- Graham wrote: ----

I have a procedure detailed below which was created, thanks to support fro
this group, to remove highlighted areas from worksheets before printing a
area and then after printing to return the sheet to the areas highlighted a
before. This works fine but there is a problem which can occur in tw
situations. If the procedure is activated (from a button ) when a printer i
not connected or if a printer error occurs during printing (which doe
happen on some network printers), then the sheet is left with the area
without the highlights. ie the line to restore the highlights is after th
print command as you will see. Is there a way to error trap for this type o
situation? I as always value any help or guidance

Sub printmargins(
Dim myRng As Rang
Dim myColorIndex As Lon
Dim sh As Workshee
For Each sh In ThisWorkbook.Worksheet
If sh.Range("C2").Value > 0 The
Set myRng = Nothin
On Error Resume Nex
Set myRng = sh.Range(sh.Name
On Error GoTo
If myRng Is Nothing The
'do nothing, it didn't have a range with the worksheet nam
Els
myColorIndex = myRng(1).Interior.ColorInde
myRng.Interior.ColorIndex = xlNon
With sh.PageSetu
.Orientation = xlPortrai
.FitToPagesWide =
.FitToPagesTall =
.LeftMargin = Application.InchesToPoints(0.551181102362205
.RightMargin = Application.InchesToPoints(0.15748031496063
End Wit
sh.Range("PRGM").PrintOu
myRng.Interior.ColorIndex = myColorInde
End I
End I
Next s
End Su

Kind regards
Graham Haugh
Turriff, Scotlan

 

[Graham]

Brad,
Thanks for the full explanation which has helped me get my head
round it. When I modified the procedure it still did not carry out the next
statement....
myRng.Interior.ColorIndex = myColorIndex
....after the print "failure". I then changed the "On Error GoTo 0" to GoTo
Handler and put the ColorIndex code in the Handler and this did the trick,
or should I say it works, and the cells are re-highlighted, after the
message box is closed. I hope this is what should be done.
I am very grateful for your help.

Graham

In the code you use earlier in the procedure you can find the answer to

your question. The 'On Error Resume Next' statement says to just skip over
any errors. So when it tries to assign the Sheet name to a name that may
not exist, it would just skip that error. Then the programmer who wrote
that code had errors turned back on with the statement 'On Error GoTo 0'.
So you can precede and proceed any code you expect to have an error with
those statements.

For example:
On Error Resume Next
sh.Range("PRGM").PrintOut
On Error GoTo 0

You might want to evaluate the error code however, so you can notify your

user that there was a printing problem. But since we ignored errors earlier
in the procedure, we need to clear (the Err object's Clear method) the Err,
so we can evaluate properly. Consider these changes:

 

[Tom Ogilvy]

The onerror statement clears the error - you don't need the added err.clear
in your example.


For example:

Sub TestErrClear()
On Error Resume Next
Err.Raise 1004
Debug.Print Err.Number
On Error Resume Next
Debug.Print "after resume", Err.Number
End Sub

--
Regards,
Tom Ogilvy

In the code you use earlier in the procedure you can find the answer to

your question. The 'On Error Resume Next' statement says to just skip over
any errors. So when it tries to assign the Sheet name to a name that may
not exist, it would just skip that error. Then the programmer who wrote
that code had errors turned back on with the statement 'On Error GoTo 0'.
So you can precede and proceed any code you expect to have an error with
those statements.

For example:
On Error Resume Next
sh.Range("PRGM").PrintOut
On Error GoTo 0

You might want to evaluate the error code however, so you can notify your

user that there was a printing problem. But since we ignored errors earlier
in the procedure, we need to clear (the Err object's Clear method) the Err,
so we can evaluate properly. Consider these changes: