Create Folder Picker in UserForm with Excel VBA

To create a folder picker in a UserForm using Excel VBA, you can use the FileDialog object, which allows users to select a folder. Here’s a detailed explanation of the code to implement a folder picker in a UserForm, step by step:

Step 1: Set up the UserForm

1. Open the VBA editor (press Alt + F11).
2. In the VBA editor, go to Insert > UserForm to create a new UserForm.
3. Add a CommandButton to the UserForm. You can name it something like cmdSelectFolder.
4. Optionally, add a TextBox or a Label to display the selected folder path (name it txtFolderPath or lblFolderPath).

Step 2: VBA Code to Open Folder Picker

In this step, we’ll write the VBA code that opens the folder picker dialog when the user clicks the button.

1. Double-click the CommandButton (cmdSelectFolder) in the UserForm to open its Click event.
2. Add the following code to the Click event of the CommandButton.

Full Code Example:

Private Sub cmdSelectFolder_Click()
    Dim folderPath As String
    Dim dialog As FileDialog
    ' Initialize the FileDialog object to pick a folder
    Set dialog = Application.FileDialog(msoFileDialogFolderPicker)   
    ' Set the dialog title (optional)
    dialog.Title = "Select a Folder"  
    ' Show the dialog
    If dialog.Show = -1 Then
        ' If the user selects a folder (OK is clicked), get the folder path
        folderPath = dialog.SelectedItems(1)       
        ' Display the selected folder path in the TextBox (or Label)
        Me.txtFolderPath.Value = folderPath
    Else
        ' If the user cancels the dialog, display a message (optional)
        MsgBox "No folder selected", vbInformation, "Selection Cancelled"
    End If
    ' Clear the dialog object
    Set dialog = Nothing
End Sub

Explanation of the Code:

1. Create a FileDialog Object:
   • Set dialog = Application.FileDialog(msoFileDialogFolderPicker): This creates a FileDialog object that will allow the user to pick a folder, not a file. The constant msoFileDialogFolderPicker is specifically used for folder selection.
2. Dialog Title:
   • dialog.Title = « Select a Folder »: This sets the title of the folder picker dialog to “Select a Folder.”
3. Show the Dialog:
   • If dialog.Show = -1 Then: This opens the folder picker. If the user selects a folder and clicks « OK, » the Show method returns -1, meaning a folder was selected.
4. Retrieve Folder Path:
   • folderPath = dialog.SelectedItems(1): This retrieves the folder path that the user selects. The SelectedItems collection contains the selected folder, and 1 is used to get the first (and only) item.
5. Display the Path:
   • Me.txtFolderPath.Value = folderPath: This sets the Value of a TextBox (or a Label) to show the selected folder path.
6. Cancel Handling:
   • If the user cancels the folder selection, the dialog shows a message box with the message « No folder selected. »
7. Clean up:
   • Set dialog = Nothing: This ensures that the dialog object is properly cleared from memory after it’s used.

Step 3: Show the UserForm

To show the UserForm, you can call it from a module or a button on a worksheet. Here’s how you can do it from a simple button in a worksheet:

1. Go to a worksheet and add a Button (from the Developer tab > Insert > Button).
2. Assign a macro to the button.
3. In the assigned macro, add the following code to show the UserForm:

Sub ShowFolderPickerForm()

    UserForm1.Show

End Sub

Now, when you click the button, the UserForm with the folder picker will appear.

Additional Notes:

• The FileDialog object is powerful and can be used for file selection as well, but for folder picking, you must use the msoFileDialogFolderPicker constant.
• The TextBox or Label (txtFolderPath) will show the path of the selected folder. If you are using a Label, you can use Me.lblFolderPath.Caption = folderPath instead.

Summary:

This code creates a simple, functional folder picker in an Excel UserForm. It uses the FileDialog object to open a dialog where the user can select a folder, and then displays the folder path in a TextBox or Label for further processing.