ErrorHandling - RunTime Error 5 on Second Run

ScottDoom

New Member
Joined
Dec 31, 2015
Messages
7
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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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
 
Upvote 0
Thank you. Both options work with repeated clicks of Cancel on the FileDialog.

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.
 
Upvote 0
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.

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.
 
Upvote 0

Forum statistics

Threads
1,214,120
Messages
6,117,845
Members
448,782
Latest member
lepaulek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top