Excel VBA Open Msg Title

Question

I'm trying to prompt the user to open a file, but the title says something other than Open. I know that there exists MsgBox and a function to prompt open(that I'm not familiar with). I was wondering if there exists some combination of the MsgBox and that function so that instead of the open title on the open prompt I can insert my own instruction. The file collected from this function would then be collected for later use.

I'm trying to avoid inserting both the MsgBox and another function for the open prompt. If this is possible any assistance is appreciated, if not thank you anyways.

Answer 1

I believe you are looking for a FileDialog as per John's comment.

The example below will show a FileDialog and return the path of the file selected. If the dialog is cancelled, the return value will be vbNullString.

You could pass the default path as a parameter to the function as well as the file extension for the Filter.

---

Public Function OpenFileDialogToString() As String
    On Error GoTo Trap

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Title = "Dialog Title"
        .InitialFileName = "Dialog start-up path"
        .Filters.Clear
        .Filters.Add "Text Documents (*.txt)", "*.txt", 1
        .ButtonName = " Open "
        .AllowMultiSelect = False
    End With

    If fd.Show <> 0 Then OpenFileDialogToString = fd.SelectedItems(1)

Leave:
    Set fd = Nothing
    On Error GoTo 0
Exit Function

Trap:
    MsgBox Err.Description, vbCritical 
    Resume Leave
End Function

---

To call it:

Dim path As String
path = OpenFileDialogToString()

If path <> vbNullString Then Debug.Print path