How to Program a Browse Button in Excel VBA to Get a Folder Name

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Program a Browse Button in Excel VBA to Get a Folder Name
Search for a folder path in Excel using VBA. (Stockbyte/Stockbyte/Getty Images)

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:
Moderate

Other People Are Reading

Instructions

  1. 1

    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.

  2. 2

    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.

  3. 3

    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:

  4. 4

    Type the following code to define two variables you will use in your program:

    Dim path Selected As String
    
    Dim ShellApp As Object
    
  5. 5

    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)
    
  6. 6

    Type the following to get the folder selected in the "BrowseForFolder" dialogue window:

    path Selected = ShellApp.self.Path
    
  7. 7

    Type the following to add the path selected to the text box control on your form:

    Me.TextBox1.Text = path Selected
    
  8. 8

    Type the following to release the object from memory:

    Set ShellApp = Nothing
    
  9. 9

    Type the following to display a message box with any possible errors and end the procedure:

    Exit_CommandButton1_Click:

    Exit Sub
    

    Err_CommandButton1_Click:

    MsgBox Err.Description
    
    Resume Exit_CommandButton1_Click:
    

    End Sub

  10. 10

    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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.