Please help with inserting hyperlink

[ryssa]

Hi there,
I have a form with many data entry fields and there is a button "Lin
To File" which is supposed to open the insert hyperlink pop up box
When I select the file I want, the address of the file is supposed t
appear in a textbox "txtLinkToFile" and clicking the OK button, th
hyperlinked file name without the complete address has to appear in th
cell.

All that I managed to do is open the pop up box. If someone could hel
out, that would be great.

Below is just a part of the code that I am using.

Thanks all.


Code
-------------------

Private Sub UserForm_Initialize()
'...
'...
txtLinkToFile.Value = ""
End Sub

Private Sub cmdSave_Click()
With Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'...
'...
.Offset(0, 51) = txtLinkProjectList.Value
End With
End Sub

Private Sub cmdLinkToFile_Click()
'Open hyperlink pop up box
Application.Dialogs(xlDialogInsertHyperlink).Show
End Sub

 

[Patrick Molloy]

A hyperlink is supposed to open the file/folder that its
set to...so the behaviour you get is as expected.

I suspect that you are using a form to select a file, and
then you want the file name in a cell and the cell
hyperlinked to the file?

try this in a standard module:

Sub Fetch()

Dim fn As String

fn = Application.GetOpenFilename

If fn = "False" Then Exit Sub

Range("B2").Hyperlinks.Add Range("B2"), _
fn, , , FileName(fn)


End Sub
Private Function FileName(sFileName As String)
Dim pos As Long
pos = InStrRev(sFileName, "\")
FileName = Mid(sFileName, pos + 1)
End Function


HTH

Patrick Molloy
Microsoft Excel MVP