CODEWITHSUNDEEP
excelvba
CallumBrown
CallumBrown

Reputation: 15

Excel VBA Dialog Box

I have the following code:

Public Function get_workbook() As String
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = False
    fd.Title = "Please select the file."
    fd.Show
    get_workbook = fd.SelectedItems(1)
End Function

This works. However, if the user closes the dialog box once opened, there is an: "Invalid procedure call or argument" error thrown.

How would I go about handling this error? So for example, rather than that error being thrown, the function simply exits.

EDIT:

I use this code to get the user to open a file which also works.

Dim wb as Workbook
Set wb = Workbooks.Open(get_workbook(), ReadOnly:=True)

Upvotes: 0

Views: 363

Answers (1)

Warcupine
Warcupine

Reputation: 4640

Add in an error handler. I used a select case so you can add other errors should they occur.

Public Function get_workbook() As String
    On Error GoTo errcatch 
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = False
    fd.Title = "Please select the file."
    fd.Show
    get_workbook = fd.SelectedItems(1)
    on error goto 0
    Exit Function 'Stop code from going into error handler without an error
errcatch:
    Select Case Err.Number 'Do something based on error number
        Case 5  
            on error goto 0
            Exit Function
    End Select
End Function

Upvotes: 2

Related Questions