Consulting

Results 1 to 7 of 7

Thread: Understanding the Syntax of Hyperlinks.Add

  1. #1
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    218
    Location

    Understanding the Syntax of Hyperlinks.Add

    Good day,

    I would love to understand the difference, other than what is given below, between Address and SubAddress. I notice that Address is normally given as "", why and what else can be put in there? The descriptions are too vague for me.

    Please help to clarify.

    Regards,
    vanhunk


    expression
    .Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)

    expression A variable that represents a Hyperlinks object.
    Parameters
    Name
    Required/Optional
    Data Type
    Description
    Anchor Required Object The anchor for the hyperlink. Can be either a Range or Shape object.
    Address Required
    String The address of the hyperlink.
    SubAddress Optional Variant The subaddress of the hyperlink.
    ScreenTip Optional Variant The screen tip to be displayed when the mouse pointer is paused over the hyperlink.
    TextToDisplay Optional Variant The text to be displayed for the hyperlink.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    The way I use it

    1. Link to something in current workbook: Address = blank, Subaddress = Cell address (need single quotes if spaces in WS name)
    2. Link to web site: Address = URL, Subaddress = "" (has to be 2 double quotes for some reason)
    3. Link to something in another WB or document: Address = file path, Subaddress = bookmark or cell


    Probably lots of small things, but this seems to work

    Option Explicit
    Sub demo()
        'http://msdn.microsoft.com/en-us/library/office/ff837214.aspx
        Call ActiveSheet.Hyperlinks.Add(ActiveSheet.Cells(2, 2), vbNullString, ActiveSheet.Cells(5, 5).Address, "Go to E5", "This goes to E5")
        'have to use "" and not vbNullString
        Call ActiveSheet.Hyperlinks.Add(ActiveSheet.Cells(5, 2), "http:\\www.google.com", "", "Go to Google's site", "This goes to Google")
        Call ActiveSheet.Hyperlinks.Add(ActiveSheet.Cells(8, 2), "C:\My Documents\MyFile.doc", "MyBookMark", "Go to Doc", "This goes to a MS Word Document Bookmark")
    End Sub

    Paul

  3. #3
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    218
    Location
    Thank you Paul,

    I appreciate your quick response. I will use it in the way you suggested.

    Regards,
    vanhunk

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    Others might some suggestions also

    Paul

  5. #5
    VBAX Newbie
    Joined
    Jul 2019
    Posts
    2
    Location
    Question:

    I'm trying to make a hyperlink to a CELL in a DIFFERENT WORKSHEET in the SAME WORKBOOK. However, I need the CELL value to change according to a variable (since I'm automating and the cell value will change).


    Would you know how to refer to a different worksheet in the hyperlink with the syntax below?

    Call ActiveSheet.Hyperlinks.Add(ActiveSheet.Cells(2, 2), vbNullString, ActiveSheet.Cells(5, 5).Address, "Go to E5", "This goes to E5")

  6. #6
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    Welcome to the forum -- take a minute to look at the FAQs at the link in my sig.

    As an aside, this thread was 6 years old and marked SOLVED so it might be better to start a new one rather than tag onto such an old one

    Option Explicit
    
    Sub Hyper()
        Dim rHyperlink As Range, rDest As Range
        
        Set rHyperlink = Worksheets("Sheet1").Cells(2, 2)
        Set rDest = Worksheets("Sheet2").Cells(5, 5)
        
        Call rHyperlink.Parent.Hyperlinks.Add(rHyperlink, vbNullString, "'" & rDest.Parent.Name & "'!" & rDest.Address, _
            "Link to " & rDest.Address(False, False), "This goes to " & "'" & rDest.Parent.Name & "'!" & rDest.Address(False, False))
     
    
    
       Set rHyperlink = Worksheets("Sheet1").Cells(6, 6)
        Set rDest = Worksheets("Sheet3").Cells(15, 15)
        
        Call rHyperlink.Parent.Hyperlinks.Add(rHyperlink, vbNullString, "'" & rDest.Parent.Name & "'!" & rDest.Address, _
            "Link to " & rDest.Address(False, False), "This goes to " & "'" & rDest.Parent.Name & "'!" & rDest.Address(False, False))
    
    End Sub
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Newbie
    Joined
    Jul 2019
    Posts
    2
    Location

    Talking WORKS

    IT WORKS! Thank you so much.

    Going forward, I'll keep your forum tips in mind. I'm honestly not used to asking for help in forums.

    Thank you again.



    Quote Originally Posted by Paul_Hossler View Post
    Welcome to the forum -- take a minute to look at the FAQs at the link in my sig.

    As an aside, this thread was 6 years old and marked SOLVED so it might be better to start a new one rather than tag onto such an old one

    Option Explicit
    
    Sub Hyper()
        Dim rHyperlink As Range, rDest As Range
        
        Set rHyperlink = Worksheets("Sheet1").Cells(2, 2)
        Set rDest = Worksheets("Sheet2").Cells(5, 5)
        
        Call rHyperlink.Parent.Hyperlinks.Add(rHyperlink, vbNullString, "'" & rDest.Parent.Name & "'!" & rDest.Address, _
            "Link to " & rDest.Address(False, False), "This goes to " & "'" & rDest.Parent.Name & "'!" & rDest.Address(False, False))
     
    
    
       Set rHyperlink = Worksheets("Sheet1").Cells(6, 6)
        Set rDest = Worksheets("Sheet3").Cells(15, 15)
        
        Call rHyperlink.Parent.Hyperlinks.Add(rHyperlink, vbNullString, "'" & rDest.Parent.Name & "'!" & rDest.Address, _
            "Link to " & rDest.Address(False, False), "This goes to " & "'" & rDest.Parent.Name & "'!" & rDest.Address(False, False))
    
    End Sub

Posting Permissions

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