How do I generate a dynamic hyperlink using worksheet R1C1 coordinates

Question

0.00/5 (No votes)

See more:

Excel-2016

I have a unique need for a dynamic hyperlink but not related to text that you can match. I want to be able to enter R1C1 Coordinates in a cell and hyperlink to that cell within the same worksheet. I don’t believe it can be done with the standard hyperlink because the coordinates will vary, I think this needs to be created as a macro and assigned a button or some other form. if necessary, we might have to separate the Column and Row entries, example;
instead of BC2006 entered in the same cell, enter the column "BC" and row "2006" in separate cells

What I have tried:

Routine hyperlink functions. I've also tried using the "Address" function in conjunction with the hyperlink as a formula but that didn't work. The issue is that I need Excel to view the entry as a R1C1 Location and not text, and therefore enabling Excel to identify the coordinates before executing some form of "GoTo" command.

Posted 12-May-20 14:30pm

RStPierre

Updated 12-May-20 21:20pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-05-12T21:21:00

If i understand you well...

Try this:

VB

Option Explicit

Sub Test()
    'call AddHyperlink procedure
    AddHyperlink ThisWorkbook.Worksheets(1), "A5", "BC2006", "Go!"

End Sub

Sub AddHyperlink(wsh As Worksheet, insertInto As String, saddress As String, text2display As String)
    'adds hyperlink to a specific cell 
    wsh.Hyperlinks.Add Anchor:=wsh.Range(insertInto), _
        Address:="", _
        SubAddress:=wsh.Name & "!" & wsh.Range(saddress).AddressLocal, _
        ScreenTip:=text2display, _
        TextToDisplay:=wsh.Range(saddress).AddressLocal(ReferenceStyle:=xlR1C1)
End Sub