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
In the VBA Editor, start by inserting a UserForm.
Build the UserForm
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.
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”
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.
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.
Ahh.. perfect. Thank you