Create hyperlink via userform

[ldarley]

Hi all

Not sure what is going on with this, I need my code to add a user selected hyperlink through the dialogue box, into a table on a different worksheet. I don't want to overwrite the current cell value with the screen tip either,

What it is doing, is adding the root part of the hyperlink (with \True on the end) to the correct cell but then the last part of the hyperlink - from desktop\ onward to the current active cell (along with writing the screen tip to that cell).

Can anyone point out what i've got wrong?

Private Sub CommandButton3_Click()


Dim ws As Worksheet
Dim tbl As ListObject
Dim CurrentRef As String
Dim i As Integer
    
    Set ws = Dossier
    Set tbl = ws.ListObjects("Dossier")
        
        'create hyperlink in userform
        CurrentRef = txtprojectref.Value
        i = Right(CurrentRef, 1)
        ws.Hyperlinks.Add Anchor:=tbl.DataBodyRange(i, 1), Address:=Application.Dialogs(xlDialogInsertHyperlink).Show
   
   
 End Sub

 

[sijpie]

The problem is in the way that you use the Application.Dialog(xldialoginserthyperlink) .
This method will insert the hyperlink and tooltip to the active cell.

If you want to use the ws.Hyperlinks.Add method, then you need to capture the hyperlink in a different way as a string.

What are the hyperlinks? files or web links? or locations in a file?

If they are files then you can use the file picker dialog to get the file name, and then build your hyperlink from that.

 

[sijpie]

As an alternative, you can store the current cell address. Then select the cell that needs the hyperlink. Then only use 'Application.Dialogs(xlDialogInsertHyperlink).Show' . Next restore to the saved cell address