Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    on error bypassed (2003 SP2)

    I had a similar problem to this before but this is slightly different:

    I have a worksheet that calls another file - 'Offspring.xls'

    It then executes code within this file, which loops through each sheet and does stuff. This works fine. As one or more sheets from this file may be removed by users, I have tried to incorporate an onerror command but - and here's the weird thing - the first on error command works OK, but the second time it hits, the code ignores it. Can you please advise what is wrong?
    I've simplified the code I'm using as follows:

    Sub Updatef()

    'Do stuff on the tables sheet - if tables sheet does not exist, go to chairs sheet
    On Error GoTo Chairs

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: on error bypassed (2003 SP2)

    I'd use something like this:

    Sub Updatef()
    Dim n As Integer
    'Do stuff on the tables sheet - if tables sheet does not exist, go to chairs sheet
    On Error GoTo ErrHandler
    n = 1
    'do stuff on tables sheet

    Chairs:
    n = 2
    'do stuff on chairs sheet

    Sets:
    n = 3
    'do stuff

    Chaises:
    n = 4
    'do stuff

    Umbrellas:
    n = 5
    'do stuff

    BaseT:
    n = 6
    'do stuff

    Misce:
    'do stuff

    Exit Sub

    ErrHandler:
    Select Case n
    Case 1
    Resume Chairs
    Case 2
    Resume Sets
    Case 3
    Resume Chaises
    Case 4
    Resume Umbrellas
    Case 5
    Resume BaseT
    Case 6
    Resume Misce
    End Select
    End Sub

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: on error bypassed (2003 SP2)

    I'd rewrite your code so that it uses a function to check for the existence of a sheet, rather than using lots of labels and jumping around with an error handler, which is not good practice.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on error bypassed (2003 SP2)

    Hans has shown you how to properly use On Error, which requires the use of Resume after an error has occurred to exit from interrupt mode. However, using this method will have another, possibly bad, side affect. Using On Error this way is going to skip out of the processing of a sheet on ANY error, not just if the sheet is missing, without letting you know that an error occurred. I would recommend checking if the sheet exists before processing it and letting other errors still trigger an error condition. Something like this:

    <code>
    Dim oSH As Worksheet
    On Error Resume Next
    Set oSH = Nothing
    Set oSH = Worksheets("Tables")
    On Error GoTo 0
    If Not oSH Is Nothing Then
    'do tables sheet stuff
    End If
    </code>
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on error bypassed (2003 SP2)

    Thank you to all posts - very helpful. I managed to get each solution to work, and opted for Legare's solution for the reasons she suggested.
    Thanks also for explaining the requirement of 'resume' when using on error - I will remember that.
    Thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: on error bypassed (2003 SP2)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Legare is a he <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on error bypassed (2003 SP2)

    ummm. oooops! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> sorry, Legare!
    Thanks for the heads up, Hans! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on error bypassed (2003 SP2)

    Not a problem. I also know at least two Legares who are shes. It is hard to tell from my userpic. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •