Results 1 to 3 of 3

Thread: Create hyperlink via userform
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2012
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create hyperlink via userform

    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 by ldarley; Sep 20th, 2019 at 06:29 AM.

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,462
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Create hyperlink via userform

    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.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,462
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Create hyperlink via userform

    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
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •