Browsing To

To display a folder location and/or file location you can use the ComboBox control.
By changing the "DropButtonStyle" to fmDropButtonStyleEllipsis we can create a control that resembles a filename box.

Add a Userform.
Add a ComboBox to the userform.
Change the Name property to "cboFileName".
Change the DropButtonStyle property to "fmDropButtonStyleEllipsis".
Add a CommandButton to the userform.
Change the Caption property to "Cancel"
Double click on the combobox and add a handler for the "DropButtonClick" event.
This will allow you to browse for a folder or file location when the ellipsis is pressed.


Selecting a File

To select a file can should use the Application.FileDialog - FilePicker method.

Private Sub cboFileName_DropButtonClick() 
Dim vFileName As Variant

'select a filename
Dim objFileDialog As Office.FileDialog
    Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFilePicker)
    
    With objFileDialog
        .AllowMultiSelect = False
        .ButtonName = "File Picker"
        .Title = "File Picker"
        If (.Show > 0) Then
        End If
        If (.SelectedItems.Count > 0) Then
            vFileName = .SelectedItems(1)
        End If
    End With

'write it to the control
   Me.cboFileName.Text = vFileName

'toggle the enabled property to move the focus to the next control
   Me.cboFileName.Enabled = False
   Me.cboFileName.Enabled = True
End Sub

Selecting A Folder

To select a folder you should use the Application.FileDialog - FolderPicker method.

'select a folder
Dim objFileDialog As Office.FileDialog
    Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFolderPicker)
    
    With objFileDialog
        .AllowMultiSelect = False
        .ButtonName = "Folder Picker"
        .Title = "Folder Picker"
        If (.Show > 0) Then
        End If
        If (.SelectedItems.Count > 0) Then
            vFileName = .SelectedItems(1)
        End If
    End With

Things to Mention

One annoying aspect of hooking the DropButtonClick event is that we can't cancel it so the control shows an empty list after we have obtained the filename.
One workaround it to toggle the Enabled property of the control which forces the focus to move to the next control in the tab order.


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext