0

I am trying to link cells from one excel sheet to another. For example, in cell Sheet1!A1 I have a hyperlink that I would like to link to Sheet3!A3,A5,A20. I have been able to link to multiple cells in the same sheet using this method: A3,A5,A20 but not to another worksheet.

To give an example with code I have been to do this:

ActiveSheet.HyperLinks.Add Range("A" & i), address:="", SubAddress:="'" & Sheet3.Name & "'!A3,A5,A20", TextToDisplay:=Cells(i, 1).Text

However, this only works when I am writing hyperlinks that link to cells within the same sheet (sheet3) as the hyperlink. This method does not work when I try hyperlinking cells in a different worksheet like so:

ActiveSheet.HyperLinks.Add Range("A" & i), address:="", SubAddress:="'" & Sheet1.Name & "'!A3,A5,A20", TextToDisplay:=Cells(i, 1).Text

Notice all I changed was the sheet#. Is there a way around this? Or is it simple impossible. I know I can link multiple cells in another sheet like so A5:A10 but that is not my goal.

Improve this question
0

2 Answers 2

Reset to default
2

You have to put the sheet in front of every reference when you're going to a new sheet. When you only put it in the first one, it's like saying "Go to sheet1!A1 and Sheet2!A10" at the same time, which doesn't work. It works on the same sheet because the unqualified references point to the same sheet as the qualified one. Here's an example.

Sub test()
    
    Sheet2.Hyperlinks.Add _
        Sheet2.Range("F10"), Address:="", SubAddress:="'" & Sheet1.Name & "'!A3," & "'" & Sheet1.Name & "'!A5," & "'" & Sheet1.Name & "'!A20", _
        TextToDisplay:=Sheet1.Cells(1, 1).Text
    
End Sub

Mine's not in a loop like yours, but I assume you can adapt. If not, post back and I can help further.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

It works almost perfectly, however if I add more than 11 cells I seem to run into an error: Reference isn't valid. If I type in a string like so: SubAddress:="'Documentation'!$D$83,'Documentation'!$D$84,'Documentation'!$D$85,'Documentation'!$D$86,'Documentation'!$D$87,'Documentation'!$D$95,'Documentation'!$D$89,'Documentation'!$D$90,'Documentation'!$D$91,'Documentation'!$D$92,'Documentation'!$D$101" It works, but if I add one more cell reference it gives me an error.
The error appears to be caused due to a character limit in the parameter itself, is there a way to work around this?
@user3880490 this is the answer to your question, you should accept it. Working around the character limit might make another good question, once you've done your research
0

Please, try the next solution. It uses the hyperlink only to call a function able to extract the sheet and the range from the hyperlink TextToDisplay:

  1. Copy the next Sub in a standard module (to create a hyperlink). The hyperlink can be manually created, too:
Sub testCreateHyperlink()
   ActiveSheet.Hyperlinks.Add Anchor:=Range("a20"), Address:="", _
         ScreenTip:="Select discontinue range in anohter sheet", _
                         TextToDisplay:="Documentation|A4,A7,A8,A15,B20"
End Sub

Many such hyperlinks can be created. The actual rule runs a macro for hyperlinks in columns 1, but the code can easily be adapted according to your need.

  1. In the sheet module, where the hyperlink(s) exist, copy the next event code:
Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Column = 1 Then
        Dim sh As Worksheet

        Set sh = Worksheets(Split(Target.Range.Value, "|")(0)): sh.Activate
        sh.Range(Split(Target.Range.Value, "|")(1)).Select
    End If
End Sub
  1. Click the above created hyperlink(s) and selection will be done in the combination 'sheet name|range' kept by clicked cell TextToDisplay hyperlink.

In this way, the string keeping the range address can be much longer (not needing the sheet name for each continuous range, plus concatenation characters). If the limitation of 256 characters is reached, I have in mind a solution to solve such an issue, too...

  1. In order to rapidly/easily fill the TextToDisplay at hyperlink creation, some functions can be imagined. The next one creates the necessary string from selected cells:
Private Function strSelTextToD() As String
    Dim sh As Worksheet: Set sh = ActiveSheet
    
    strSelTextToD = sh.Name & "|" & Selection.Address(0, 0)
End Function

And following one, create it from cells having interior green colored (65280):

Private Function strTextToD() As String
   Dim sh As Worksheet, Cel As Range, rng As Range
   Set sh = ActiveSheet
   For Each Cel In sh.UsedRange.cells
        If Cel.Interior.Color = vbGreen Then
           If rng Is Nothing Then
                Set rng = Cel
           Else
                Set rng = Union(rng, Cel)
           End If
        End If
   Next
   If Not rng Is Nothing Then strTextToD = sh.Name & "|" & rng.Address(0, 0)
End Function

The above two functions can be called in the next way:

Sub testStrHypTTD()
 Dim strToDisp As String
  strToDisp = strSelTextToD: Debug.Print strToDisp
  strToDisp = strTextToD: Debug.Print strToDisp
End Sub

And the returned string can be simple used, in the Sub creating the hyperlink, like this:

'...
TextToDisplay:=strSelTextToD
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.