Macro to prompt for hyperlink path

[Chrismar1410]

Please see my image below - I want to record a macro that allows me to insert a hyperlink but stop before I choose the actual link I want to use as every line will have a different link

 

[PeteWright]

Try this:

Public Sub LinkCell()
   Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
   Dim fil As Object
   Dim filename As String
   Dim path As String
   Dim text As String
   Dim diaFile As FileDialog
   Dim col As New Collection
   Dim cell As Range
   col.Add Application.InputBox(Prompt:="Choose cell to add hyperlink", Title:="Select Cell", Type:=8)
   If TypeOf col(1) Is Range Then
      Set cell = col(1)
      Set diaFile = Application.FileDialog(msoFileDialogFilePicker)
      With diaFile
         .Title = "Select file to be linked"
         .AllowMultiSelect = False
      End With
      If diaFile.Show = 0 Then Exit Sub
      Set fil = fso.GetFile(diaFile.SelectedItems.Item(1))
      path = fil.path
      filename = Left(fil.Name, InStrRev(fil.Name, ".") - 1)
      text = Application.InputBox( _
         Prompt:="Please type in the text to be displayed for the link", _
         Title:="Hyperlink Text", _
         Default:=filename)
      If text = False Then text = filename
      cell.Hyperlinks.Add Anchor:=cell, Address:=path, TextToDisplay:=text
   End If
End Sub

Place this Macro in a (new) Module and run it. This is what it does:

1. Select a cell in a Worksheet (where the Hyperlink should be added)
2. Select a file (which should be linked to the previously selected cell)
3. Enter a text (Hyperlink text, that should be displayed in the cell)

Please let me know if this is what you need.