CODEWITHSUNDEEP
excelvba
Greg
Greg

Reputation: 31

The second of 2 'On Error goto ' statements gets ignored

I have some code that tries to set 11x17 paper as a default...

        On Error GoTo PageSizeErr
        ActiveSheet.PageSetup.PaperSize = xlPaperTabloid

' more code here

PageSizeErr:
    On Error GoTo PageErr2
    ActiveSheet.PageSetup.PaperSize = xlPaper11x17  'try another 11x17 driver definition
    GoTo resumePrinting
PageErr2:
    MsgBox ("There's a problem setting Tabloid paper for the printer you have selected." & Chr(10) _
    & "If you have an 11x17 printer selected, please contact EMBC, otherwise, try a different printer.")
    Exit Sub

-------------- end of code sample -----------------

When it gets to the second 'ActivateSheet.PageSetup... line, instead of going to PageErr2 lable I get an error dialog box. (I have a printer selected that doesn't support 11x17 which is what I'm trying to test for.)

The multiple error handlers are needed as it seems that different printer drivers handle the setting the differently.

Why doesn't the second 'On Error goto ' statement get recognized?

Upvotes: 3

Views: 6515

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

You can't use on error goto within an error handler. See http://www.cpearson.com/excel/errorhandling.htm

Maybe try something like this:

Sub Tester()

Dim pSize As XlPaperSize

    pSize = xlPaperTabloid


    On Error GoTo haveError:
    ActiveSheet.PageSetup.PaperSize = pSize
    'print stuff...

    Exit Sub

haveveError:
    If pSize = xlPaperTabloid Then
        pSize = xlPaper11x17
        Resume
    End If
    MsgBox ("Couldn't print using tabloid or 11x17")

End Sub

Upvotes: 3

Related Questions