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.

microsoft excel docs

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

Private Const BIF_RETURNONLYFSDIRS As Long = &H1 
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

    hWnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type

Declare PtrSafe Function SHGetPathFromIDListA Lib "shell32.dll" (ByVal pidl As Long, ByVal pszBuffer As String) As Long
Declare PtrSafe Function SHBrowseForFolderA Lib "shell32.dll" (lpBrowseInfo As BrowseInfo) As Long

Public Function BrowseFolderAPI(Optional Caption As String = "") As String
Const sProcName As String = "BrowseFolderAPI"

Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long
Dim response As VbMsgBoxResult
   On Error GoTo ErrorHandler
   With BrowseInfo
      .hOwner = 0
      .pidlRoot = 0
      .pszDisplayName = String(MAX_PATH, vbNullChar)
      .lpszINSTRUCTIONS = Caption
      .lpfn = 0
   End With
   FolderName = String(MAX_PATH, vbNullChar)
   ID = SHBrowseForFolderA(BrowseInfo)
   If ID Then
      Res = SHGetPathFromIDListA(ID, FolderName)
      If Res Then
          BrowseFolderAPI = Left(FolderName, InStr(FolderName, vbNullChar) - 1)
      End If
   End If
   Exit Function
   Call Error_Handle(msMODULENAME, sProcName, Err.Number, Err.Description)
End Function


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.

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