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. :doh:

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 (http://msdn.microsoft.com/en-us/library/office/ff838238.aspx) or Shape (http://msdn.microsoft.com/en-us/library/office/ff835842.aspx) 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