The BrowseForFolder method can make your Microsoft Office Excel application more dynamic by not requiring you to hard code paths in your program. The BrowseForFolder method allows you to prompt the user to browse for a particular folder in your computer using Visual Basic for Applications (VBA). The folder path returned can be used for other programming purposes such as reading or saving a file. Reduce maintainability by not having to hard code any paths in your program.
- Skill level:
Other People Are Reading
Launch Excel, click the "Developer" tab followed by "Visual Basic." Click the "Insert" menu and select "UserForm" to insert a new Form. Click the "View" menu and click "Toolbox" to view controls you can add to your Form.
Click "CommandButton" on the Toolbox and click then drag on your form to create button control. Add a new text box control using the same technique.
Double-click the new button to open the Visual Basic Editor Window. Type the following code below "Private Sub CommandButton1_Click():"
On Error GoTo Err_CommandButton1_Click:
Type the following code to define two variables you will use in your program:
Dim path Selected As String Dim ShellApp As Object
Type the following code to start the "BrowseForFolder" method and display a dialogue window for the user to choose a folder:
Set ShellApp = CreateObject("Shell.Application"). _ BrowseForFolder(0, "Choose a folder", 0, OpenAt)
Type the following to get the folder selected in the "BrowseForFolder" dialogue window:
path Selected = ShellApp.self.Path
Type the following to add the path selected to the text box control on your form:
Me.TextBox1.Text = path Selected
Type the following to release the object from memory:
Set ShellApp = Nothing
Type the following to display a message box with any possible errors and end the procedure:
MsgBox Err.Description Resume Exit_CommandButton1_Click:
Press "F5" to run your program then click "CommandButton1" to browse for a folder. Choose a folder and click "OK." The path will be displayed in the text box control.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for