Hyperlink

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lk As Hyperlink

How can I have this open up the range in the order it is listed? It opens
the hyperlinks in a different order. Like A10, A3, A21, A7.

'If you select A30
If Not Intersect(Target, Range("A30")) Is Nothing Then
'for all hyperlink in this range
For Each Lk In
Range("A2,A3,A4,A6,A7,A9,A10,A13,A14,A15,A20,A21,A24,A27,A28,A29").Hyperlinks
'open hperlink in same window
Lk.Follow False
Next
End If
End Sub
 
I use it to view a list of sector ETF's that I have in alphabetical order on
the excel sheet. It makes it confusing to have them pop up in a different
order. It doesn't make sense that they would, but I guess the macro opens the
list randomly.
 
Try this.

Tim


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const RNGS As String = "A2,A3,A4,A6,A7,A9,A10,A13,A14," & _
"A15,A20,A21,A24,A27,A28,A29"
Dim x As Integer, arrRngs

arrRngs = Split(RNGS, ",")
'If you select A30
If Not Intersect(Target, Range("A30")) Is Nothing Then
For x = LBound(arrRngs) To UBound(arrRngs)
ActiveSheet.Range(arrRngs(x)).Hyperlink.Follow True
Next x
End If

End Sub
 
my bad:

Tim

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Const RNGS As String = "A2,A3,A4"
Dim x As Integer, arrRngs
Dim c As Range

arrRngs = Split(RNGS, ",")
'If you select A30
If Not Intersect(Target, Range("A30")) Is Nothing Then
For x = LBound(arrRngs) To UBound(arrRngs)
Me.Range(arrRngs(x)).Hyperlinks(1).Follow
Next x
End If


End Sub
 
Now it is giving me a runtime error 9
Subscript out of range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Const RNGS As String =
"A2,A3,A4,A6,A7,A9,A10,A13,A14,A15,A20,A21,A24,A27,A28,A29"
Dim x As Integer, arrRngs
Dim c As Range

arrRngs = Split(RNGS, ",")
'If you select A30
If Not Intersect(Target, Range("A30")) Is Nothing Then
For x = LBound(arrRngs) To UBound(arrRngs)
Me.Range(arrRngs(x)).Hyperlinks(1).Follow
Next x
End If


End Sub
 
Back
Top