Maximun number of hyperlinks in Excel

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

I am adding hyperlinks to a excel sheet by writing a macro. After 65502
number of hyperlinks i am not able to add the hyperlinks in excel. Is there
any limitation regarding the number of hyperlinks in a excel sheet? Here is
the macro I have used

Sub HyperLink_Test()

' Local Variables
Dim nWorkSheet As Worksheet
Dim nRange As Range
Dim strAddess As String, strDisplayText As String

' Step 1 : Loop through all worksheets
' 1a : Clear all current hyperlinks
Worksheets("Sheet1").Range("A:Z").ClearContents
' 1b : Create Linked index list

Dim i As Long
Dim j As Long


Dim count As Long

count = 0

For i = 1 To 70000

Set nRange = Worksheets("Sheet1").Range("A:Z").End(xlUp)
If (i > 1) Then
Set nRange = nRange.Offset(i - 1, 0)
End If
For j = 1 To 10



strAddess = "'" & XYZ & "'"
strDisplayText = "HyperLink : "
Worksheets("Sheet Index").Hyperlinks.Add Anchor:=nRange,
Address:="ddd", SubAddress:="", TextToDisplay:="ab"
count = count + 1
If count = 65530 Then
GoTo End1
End If
Set nRange = nRange.Offset(0, 1)

Next j


Next i

End1:



End Sub
 
Thanks Bill, yes we have to create a second worksheet, to accommodate the
hyperlinks. But it should be considered as a Microsoft Excel bug, as they
have not mentioned any where there limit on hyperlinks.
What's your opinion regarding this.
 
we have to create a second worksheet, to accommodate the hyperlinks.
But it should be considered as a Microsoft Excel bug...
Click to expand...

What about this workaround idea...

Suppose all the links are in column A, and they are "text", and not links.
Have a cell, say C1, that holds the only Hyperlink on the sheet.
C1 = "www.dummy.com"

Have a macro that, when a cell is selected in Column A, then the
Hyperlink's address (in C1) is changed to the text of the cell selected,
and the link in C1 is then followed.

This would go on the module sheet for the sheet in question.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub

Dim HL As Hyperlink

Set HL = [C1].Hyperlinks(1)
HL.Address = "http://" & Target.Value
HL.Follow
End Sub

Again, not fancy, but it may give you some ideas..
= = =
HTH
Dana DeLouis
 
Back
Top