Hyperlink

[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

 

[Tim Williams]

Why do you need to open all of those links, and in that order ?

Tim

 

[Doug]

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.

 

[Tim Williams]

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

 

[Doug]

I am recieving a runtime error 438
Object doesn't support this property or method

 

[Tim Williams]

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

 

[Doug]

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

 

[Tim Williams (Theravance)]

Maybe one of those cells doesn't have a hyperlink.

Tim

 

[Doug]

I'm really sorry I didn't catch that