How to create proper hyperlink in VBA?

Question

I have the following VBA line, which should create a hyperlink to another sheet in an Excel workbook:

Dim rMyCell As Range 
Sheets("All_Tables").Hyperlinks.Add Anchor:=Selection, Address:="Employees!" & rMyCell.Address, _
        TextToDisplay:="Link"

The hyperlinks created from this code have dollars signs placed between the letters in the cell description (such as: Employees!$A$1). How do I get rid of those dollars? The hyperlink doesn't work with them.

Thank you

CaBieberach CaBieberach · Accepted Answer · 2013-02-26T10:42:51

To use relative address instead of the Absolute (with dollar sign), use as follows:

rMyCell.Address(false,false)

The first parameter is to select if the Row should be shown as absolute. The second parameter is to select if the Columns should be shown as absolute.

If you press F1 over .Address inside the VB Editor, you will get some more info about it.

How to create proper hyperlink in VBA?

1 Answers