VBA application dialogs

[ahepple86]

I have a button on a sheet that simply opens the hyperlink dialog with Application.Dialogs(xlDialogInsertHyperlink).Show to link a cell value to an external file.

Is there a way of me using the file path that was selected during that process to do something else?

I was thinking of a function such as: Application.Dialogs(xlDialogInsertHyperlink).SelectedItems(1) but that doesn't work.

 

[Domenic]

Try the following...

Option Explicit

Sub InsertHyperlinkToFile()

    If ActiveSheet Is Nothing Then
        MsgBox "Please make sure that a worksheet is active, and try again!", vbExclamation
        Exit Sub
    End If

    Dim hyperlinkFilename As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .ButtonName = "Select"
        .InitialFileName = Application.DefaultFilePath
        .Title = "Select File"
        If .Show = 0 Then Exit Sub
        hyperlinkFilename = .SelectedItems(1)
    End With
    
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=hyperlinkFilename, TextToDisplay:="MyLink" 'change the text to display as desired
    
End Sub

Hope this helps!