CODEWITHSUNDEEP
excelvba
Mdurocher
Mdurocher

Reputation: 53

Excel file dialog box appearing multiple times

I am using Application.FileDialog(msoFileDialogOpen) to allow a user to select a file to import data from. However, after the user selects a file it pops open the file dialog box again asking them to choose a file. It will do this 2-3 times before it continues on.

Have I accidentally created a loop?

Private Sub CommandButton1_Click()
Set myFile = Application.FileDialog(msoFileDialogOpen)

With myFile
    .Title = "Choose File"

    .AllowMultiSelect = False

    If .Show <> -1 Then
       Exit Sub
       ElseIf .Show <> 0 Then
       FileSelected = .SelectedItems(1)
    End If
End With

'data location & range to copy

mydata = "='[FileSelected]'!$C$10:$C$21" '<< change as required

'link to worksheet
With ThisWorkbook.Worksheets(1).Range("C10:C21") '<< change as required
.Formula = mydata
'convert formula to text
.Value = .Value

End With
End Sub

So I have tried each of your solutions, and I am still getting two open file dialog boxes, however there is a difference between the two that I didn't notice before. the first box has the appropriate "Choose File" title, however the second box has "Update Values: FilesSelected" for the title. I also have added the last few lines that I excluded before. Thanks again for all your help.

Upvotes: 1

Views: 1732

Answers (3)

user6432984
user6432984

Reputation:

The .Show method displays the File Dialog similarly to Userform.Show. So every time you call .Show the Dialog is displayed.

With Application.FileDialog(msoFileDialogOpen)
    .Title = "Choose File"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then
        Exit Sub
    Else
        FileSelected = .SelectedItems(1)
    End If

End With

Upvotes: 3

Domenic
Domenic

Reputation: 8114

In your code, you're calling the Show method twice. Hence the dialog box displays twice. Try the following instead...

Private Sub CommandButton1_Click()
    Dim strPathAndFile As String
    With Application.FileDialog(msoFileDialogOpen)
        .Title = "Choose File"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        strPathAndFile = .SelectedItems(1)
    End With
End Sub

Hope this helps!

Upvotes: 0

Xire
Xire

Reputation: 166

you can make it shorter :

filepath = Application.GetOpenFilename("All files (*.*),*.*")
If filepath = False Then Exit Sub
MsgBox filepath

Upvotes: 1

Related Questions