VBA - File Dialog Show Result in Textbox on userform

[drluke]

I cannot find how to change this code so that the file path of the selected files are place in an existing text box (TextBox3) in my userform.

Here is the code I'm using

Private Sub CommandButton1_Click()
Dim fDialog As FileDialog, i As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
fDialog.AllowMultiSelect = True
fDialog.Title = "Select Files"
fDialog.InitialFileName = "T:\Finance\SohoMaccs\2018Maccs\Monthly Reports\"
fDialog.Filters.Add "Excel files", "*.xlsx"
If fDialog.Show = -1 Then
    For Each it In fDialog.SelectedItems
    Debug.Print it
    Next it
End If

End Sub

The above code works until files are selected. Thereafter nothing happens.

 

[DanteAmor]

Do you need this?

Private Sub CommandButton1_Click()
    Dim fDialog As FileDialog, i As Integer
    Dim n As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    fDialog.AllowMultiSelect = True
    fDialog.Title = "Select Files"
    fDialog.InitialFileName = "T:\Finance\SohoMaccs\2018Maccs\Monthly Reports\"
    fDialog.Filters.Add "Excel files", "*.xlsx"
    If fDialog.Show = -1 Then
        For Each it In fDialog.SelectedItems
            Debug.Print it
        Next it
        n = InStrRev(fDialog.SelectedItems(1), "\")
        TextBox3.Value = Left(fDialog.SelectedItems(1), n)
    End If
End Sub

Regards Dante Amor

 

[drluke]

Thank you Dante.

When I run this code I get 'Object required' error. The highlighted code is:

TextBox3.Value = Left(fDialog.SelectedItems(1), n)​

 

[DanteAmor]

You commented that you had a textbox3, you must have a textbox called textbox3 before executing the code.

 

[drluke]

You commented that you had a textbox3, you must have a textbox called textbox3 before executing the code.

I do have a textbox called TextBox3. It is in my userform which is where I want the list to be.

 

[DanteAmor]

try this

Private Sub CommandButton1_Click()
    Dim fDialog As FileDialog, i As Integer
    Dim n As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    fDialog.AllowMultiSelect = True
    fDialog.Title = "Select Files"
    fDialog.InitialFileName = "T:\Finance\SohoMaccs\2018Maccs\Monthly Reports\"
    fDialog.Filters.Add "Excel files", "*.xlsx"
    If fDialog.Show = -1 Then
        For Each it In fDialog.SelectedItems
            Debug.Print it
        Next it
        n = InStrRev(fDialog.SelectedItems(1), "\")
        msgbox Left(fDialog.SelectedItems(1), n)
    End If
End Sub

 

[drluke]

Thanks. This time it shows the correct result in a message box that opens. I want it to show the result in my userform in the textbox I have in there.

 

[Fluff]

Is the command button that is running the code on the userform?

 

[DanteAmor]

But you do not have a textbox called textbox3, so it sends the error message. You just have to put the name of some textbox that you have in your userform.
TextBox3.Value = Left(fDialog.SelectedItems(1), n)

 

[drluke]

Yes it is. Here is the comandbutton code:

Private Sub CommandButton1_Click()
Application.Run "Module2.GetWorksheetListing"
End Sub