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?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,665
Office Version
365
Platform
Windows
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
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
954
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
 

ScottDoom

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

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
954
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,858
Messages
5,489,305
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top