Excel VBA UserForm Browse for File to Attach

In this article, we will create a file browse component that can be used to browse for files in a VBA UserForm. There are many reasons you may want a user to be able to browse for a file, such as choosing a file to attach in an email or choosing a file to open to pull data from. The user will also be able to directly type or paste a file path into the textbox that we will have as part of the component, and the component will validate that the user has chosen or typed a valid file path.

Step 1: Building the UserForm

Create a New UserForm

Screenshot of adding a UserForm in the VBA editor

In the VBA Editor, start by inserting a UserForm.

Build the UserForm

The file browse userform interface screenshot in the UserForm editor

You’ll need a TextBox (named FileTextBox) and a CommandButton (named BrowseButton) to make the browse component. In my case, I’ve also added a second command button and a label which can be used to validate the file path. This is for demonstration purposes and most forms won’t need these. Feel free to add whatever other components you need as part of your macro.

Step 2: VBA Code for File Browse

Private Sub BrowseButton_Click()

Dim result As Variant

result = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If result <> False Then

    FileTextBox.Text = result
    
End If


End Sub



Function IsFileValid(path As String) As Boolean

If Dir(path) <> "" And path <> "" Then
    IsFileValid = True

Else
    IsFileValid = False
End If

End Function


Private Sub UserForm_Terminate()

If IsFileValid(FileTextBox.Text) Then
    Sheet1.Range("B1").Value = FileTextBox.Text
End If

End Sub

Private Sub ValidButton_Click()

If IsFileValid(FileTextBox.Text) Then
    FileValidLabel.Caption = "Valid!"
Else
    FileValidLabel.Caption = "Not valid!"
End If

End Sub



Above is the code for the userform. You can edit userform code by right-clicking the userform in the Project Explorer and selecting View Code. The key line is highlighted, which opens the file browse window. Once the file browse window is closed, the textbox text is set to the file path which is used to store the file path from then on. The IsFileValid function validates that a file exists. In the above example, the file path (if valid) is pasted onto the worksheet when the userform closes, but anything could be done with the file path once selected.

Conclusion

Sub LaunchForm()

UserForm1.Show

End Sub

Add a simple macro like the one above to launch the userform, and you are good to go! I hope this simple file selection component is helpful.

3 thoughts on “Excel VBA UserForm Browse for File to Attach”

  1. Ryan,
    Thank you for an excellent explanation and clean function for getting distance.
    It works great for me, so long as the origin and destination are in the same city. If not, the function returns # Value!
    For example, I am in Las Vegas. Any same city query returns a good value. A city immediately adjacent is Henderson. If this case the function fails. (Jason is installed, the References are ticked),
    Thanks in advance for your help.
    -DL

    1. Have you ensured that the distance variable is set to Variant or Long and not Int? That was an issue some people had for directions with longer distances. If so, you would need to examine the response from google but printing it out, pasting it on the sheet, or checking it out yourself in the browser to determine what the error might be.

Leave a Reply

Your email address will not be published.