CODEWITHSUNDEEP
vbauserform
user3138025
user3138025

Reputation: 825

How to include msoFileDialogFolderPicker in a userform

In a vba userform I'm prompting the user with several questions. I'm stuck with one particular prompt. I'm don't know how to include a prompt for an msoFileDialogFolderPicker within a userform.
I've used code similar to the function outlined below as an individual popup, but I don't know how to place this inside a userform. Is this possible?

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

Thanks for looking at this.

Upvotes: 0

Views: 954

Answers (1)

Thomas G
Thomas G

Reputation: 10206

Start by creating a module (in VBA window, left pane, right click, Insert..., Module). Then move your function GetFolder() in this module, declared as public

Option Explicit

Public Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

Then in your form, you want something that will call the procedure. Usually we do that with a button.

So, add a button to the form. Then double-click on the button (or right-click and "code") and it will create a click event for it.

In the button event code, call your GetFolder procedure:

Option Explicit

Private Sub CommandButton1_Click()
    Dim strFolder As String

    strFolder = GetFolder()

    MsgBox strFolder

End Sub

Upvotes: 1

Related Questions