Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Kent, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    On Error Function (97)

    I'm trying to trap a runtime error of 1004, and display a message to the user that they are unable to open this particular file because they do not have access to the drive. (Some users do some don't). The worksheet open code is as follows :-

    Workbooks.Open FileName:= _
    "ntf-gen02maindirectorysubdirectoryfilename.xls"
    Sheets("Contact Numbers").Select

    I have tried using the code from the 'On Error' example in the Help, but I cannot get it to work with 1004.

    Any ideas anyone ??
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: On Error Function (97)

    springen

    Try this for size:

    Sub OnErrorExample()
    Dim lErrorNum As Long

    lErrorNum = 0
    On Error Resume Next
    Workbooks.Open FileName:="ntf-gen02maindirectorysubdirectoryfilename.xls"
    lErrorNum = Err.Number
    If lErrorNum <> 0 Then
    MsgBox ("Sorry You can't Open this workbook...")
    Exit Sub
    End If
    Sheets("Contact Numbers").Select
    End Sub

    But there are better ways of doing this, such as checking network connections and things like that.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Location
    Kent, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On Error Function (97)

    Many thanks Wassim - that did the trick nicely.
    I'm only a beginner at this VBA stuff, so I'm trying to keep things simple.

    Thanks again

  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 Function (97)

    Wassim: Your code does not reset the On Error condition. If an error happens somewhere else in the code after this code, it could be ignored. It also give the same message, no matter what the error. I would change your code to be:

    <pre>Sub OnErrorExample()
    Dim lErrorNum As Long
    lErrorNum = 0
    On Error Resume Next
    Workbooks.Open Filename:="ntf-gen02maindirectorysubdirectoryfilename.xls"
    On Error GoTo 0
    lErrorNum = Err.Number
    If lErrorNum = 1004 Then
    MsgBox ("Sorry You can't Open this workbook...")
    Exit Sub
    Else
    MsgBox "Error " & lErrorNum & " occurred opening the file."
    End If
    Sheets("Contact Numbers").Select
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: On Error Function (97)

    Legare

    Thanks... I should have added the line you did, but I add it after the error block. It makes all these lines go together.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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