Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: ErrorHandling - RunTime Error 5 on Second Run

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ErrorHandling - RunTime Error 5 on Second Run

    I am attempting to write some error handling for the first time.

    I have written a bit which works correctly on the first go-through, but on the second run it generates a Run Time Error 5 Invalid Procedure Call or Argument.

    Code:
    Sub PickFolder()
    
    tryagn:
    With Application.FileDialog(msoFileDialogFolderPicker)
         .AllowMultiSelect = False
         .Title = "Title"
         .InitialFileName = "C:\"
         .Show
         On Error GoTo ErrHandl
         strFolPath = .SelectedItems(1)
    End With
    
    Exit Sub
    Dim iErrorResp As Integer
    
    ErrHandl:
    iErrorResp = MsgBox("Try again.", vbOkCancel)
    Select Case iErrorResp
    Case vbOK
         GoTo tryagn
    Case vbCancel
         Exit Sub
    End Select
    
    End Sub
    So if the user hits Cancel on the File Dialog the first time, the error handler correctly shows the vbOKCancel MsgBox (which in turn shows the File Dialog on OK). However, if Cancel is selected a second time (rare chance, but could happen) then strFolPath = .SelectedItems(1) generates the RunTime error. Any clues?

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,117
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    2 Thread(s)

    Default Re: ErrorHandling - RunTime Error 5 on Second Run

    How about something like this which will show the dialog until either the user selects a folder or clicks cancel in the dialog and the message box?
    Code:
    Sub PickFolder()
    Dim dlg As Object
    Dim strFolPath As Variant
    Dim iErrorResp As Integer
    
        Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
        Do
            With dlg
                .AllowMultiSelect = False
                .Title = "Title"
                .InitialFileName = "C:\"
                .Show
            End With
            If dlg.SelectedItems.Count = 0 Then
                iErrorResp = MsgBox("Try again.", vbOKCancel)
                Select Case iErrorResp
                    Case vbOK
                        ' do nothing, ie continue with loop
                    Case vbCancel
                        Exit Sub
                End Select
            End If
    
        Loop Until dlg.SelectedItems.Count > 0
    
        strFolPath = dlg.SelectedItems(1)
    
    End Sub
    If posting code please use code tags.

  3. #3
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ErrorHandling - RunTime Error 5 on Second Run

    Actually I wouldn't use Error Handling for this. I would check for the Show returned value and continue from there ( I am not changing your code, simply using how to use Show for this purpose - If Not .Show means "if Cancel is selected" here).

    Code:
    Sub PickFolder()
    
    
    tryagn:
    With Application.FileDialog(msoFileDialogFolderPicker)
         .AllowMultiSelect = False
         .Title = "Title"
         .InitialFileName = "C:\"
         If Not .Show Then GoTo ErrHandl:
         strFolPath = .SelectedItems(1)
    End With
    
    
    Exit Sub
    Dim iErrorResp As Integer
    
    
    ErrHandl:
    iErrorResp = MsgBox("Try again.", vbOKCancel)
    
    
    Select Case iErrorResp
    Case vbOK
         GoTo tryagn
    Case vbCancel
         Exit Sub
    End Select
    
    
    End Sub

  4. #4
    New Member
    Join Date
    Dec 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ErrorHandling - RunTime Error 5 on Second Run

    Thank you. Both options work with repeated clicks of Cancel on the FileDialog.

    Quote Originally Posted by smozgur View Post
    Actually I wouldn't use Error Handling for this. I would check for the Show returned value and continue from there...
    smozgur, I used your original code to test and also removed the ErrHandl section and placed the MsgBox vbOKCancel directly after the If Not .Show Then line. Thanks for this tidbit of knowledge.


    I am curious though as to why my original code was not working.

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: ErrorHandling - RunTime Error 5 on Second Run

    Because you didn't have a Resume statement to reset the original exception raised: On Error WTF? | Excel Matters

  6. #6
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ErrorHandling - RunTime Error 5 on Second Run

    Glad to hear it helps.

    That's the way I would do - using relatively native methods where possible instead of dealing with "solutions" like Error Handling serves in this sample. However, there are mostly many ways to accomplish one thing.

    Resume already explained by Rory above.

    Quote Originally Posted by ScottDoom View Post
    Thank you. Both options work with repeated clicks of Cancel on the FileDialog.


    smozgur, I used your original code to test and also removed the ErrHandl section and placed the MsgBox vbOKCancel directly after the If Not .Show Then line. Thanks for this tidbit of knowledge.


    I am curious though as to why my original code was not working.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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