Create hyperlink via userform

ldarley

Board Regular
Joined
Apr 10, 2012
Messages
106
Office Version
  1. 2019
  2. 2016
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?

Code:
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
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top