ErrorHandling - RunTime Error 5 on Second Run

[ScottDoom]

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.

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?

 

[Norie]

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?

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

 

[smozgur]

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).

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

 

[ScottDoom]

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.

 

[RoryA]

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

 

[smozgur]

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.