Understanding the Syntax of Hyperlinks.Add

[vanhunk]

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.

[Paul_Hossler]

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

[vanhunk]

Thank you Paul,

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

Regards,
vanhunk

[Paul_Hossler]

Others might some suggestions also

Paul

[ipham88]

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")

[Paul_Hossler]

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

[ipham88]

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.

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