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.
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:
Type the following code to start the "BrowseForFolder" method and display a dialogue window for the user to choose a folder:
Type the following to get the folder selected in the "BrowseForFolder" dialogue window:
Type the following to add the path selected to the text box control on your form:
Type the following to release the object from memory:
Type the following to display a message box with any possible errors and end the procedure:
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.