Test for Hyperlink

[Guest]

I need a simple Boolean function that will return TRUE if a cell contains a
hyperlink, otherwise FALSE.

For example, if A1 contained a hyperlink or contained a formula like

=HYPERLINK("http://www.cnn.com","news")

then haslink(A1) would return TRUE.

 

[Ardus Petus]

Function HasLink(rCell As Range) As Boolean
Application.Volatile
If rCell.Count > 1 Then
HasLink = CVErr(xlErrRef)
Else
HasLink = _
(rCell.Hyperlinks.Count > 0) _
Or Left(rCell.Formula, 10) = "=HYPERLINK"
End If
End Function

You must press F9 to update the results

HTH

 

[Guest]

Thank you very much.

It works perfectly.

 

[Mike Fogleman]

Public Function HasLink(rng As Range) As Boolean
If rng.Hyperlinks.Count > 0 Then
HasLink = True
Else
HasLink = False
End If
End Function

Mike F

 

[Guest]

Thanks Mike

Your code detects inserted hyperlinks, but not hyperlinks implemented by a
function call.

 

[Dave Peterson]

I'm not sure if it's important to you, but the formula doesn't have to start
with =hyperlink().

=IF(A1=1,HYPERLINK("http://microsoft.com"),HYPERLINK("http://adobe.com"))

You may want to look to see if the formula contains "hyperlink(".

 

[Guest]

Thank you Dave. All the cells being processed are either inserted
hyperlinks, or simple function calls or descriptive text.

However, I took your example and modified it slightly:

=IF(A1=1,HYPERLINK("http://microsoft.com"),12) in B2, thinking that the
value in A1 could change B2 from a hyperlink to the number 12.

When I set A1 to zero, B2 showed 12, but it was still a hyperlink!! Placing
the mouse over the cell still evoked the message to click to follow the link!!

Why is that?

 

[Dave Peterson]

I have no idea why this happens, but one way to minimize the problem of "the
address of this site is not valid..." popup is to link back to the cell
containing the formula.

So if C2 held the formula:
=IF(A1=1,HYPERLINK("http://microsoft.com"),
HYPERLINK("#"&CELL("address",C2),12))

 

[Guest]

Thanks again Dave.
--
Gary's Student

I have no idea why this happens, but one way to minimize the problem of "the
address of this site is not valid..." popup is to link back to the cell
containing the formula.

So if C2 held the formula:
=IF(A1=1,HYPERLINK("http://microsoft.com"),
HYPERLINK("#"&CELL("address",C2),12))

 

[Guest]

Can you click on a hyperlink containing an argument, that calls a macro with
the argument.

I need to pass a string or Argument to the Macro with a Hyperlink

example
in spreadsheet you have
A
1 Hyperlink
2 Supplier

would run Macro
ABC("A2")
or
ABC("Supplier")

 

[Jim Cone]

Jeff,
Link the hyperlink back to itself (so it doesn't do anything).
Then (if you use XL2000 or later) in the sheet FollowHyperlink event,
place code similar to this...

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$B$5" Then 'cell where the hyperlink is
Application.Run "'WorkbookName.ext'!YourSubName", Arg1, Arg2
End If
End Sub

(note the single quote marks)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Jeff" <[email protected]>
wrote in message
Can you click on a hyperlink containing an argument, that calls a macro with
the argument.
I need to pass a string or Argument to the Macro with a Hyperlink
example
in spreadsheet you have
A
1 Hyperlink
2 Supplier

would run Macro
ABC("A2")
or
ABC("Supplier")

 

[Guest]

Thank You Jim
Works great.

However I need to have several hyperlinks
in different cells B5 and B10 but it didn't work?

I tried this...
and Using ByVal Target or ByVal Target2 didn't work either.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$B$5" Then 'cell where the hyperlink is
Application.Run "'WorkbookName.ext'!YourSubName", Arg1, Arg2
End If
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target2 As Hyperlink)
If Target.Range.Address = "$B$10" Then 'cell where the hyperlink is
Application.Run "'WorkbookName.ext'!YourSubName", Arg1, Arg2
End If
End Sub

 

[Jim Cone]

The can be only one hyperlink event sub in the sheet module, so...

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$B$5" Then
Application.Run "'WorkbookName.ext'!YourSubName", Arg1, Arg2
ElseIf Target.Range.Address = "$B$10" Then
Application.Run "'WorkbookName.ext'!OtherSubName", Arg1, Arg2
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Jeff" <[email protected]>
wrote in message
Thank You Jim
Works great.
However I need to have several hyperlinks
in different cells B5 and B10 but it didn't work?

I tried this...
and Using ByVal Target or ByVal Target2 didn't work either.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$B$5" Then 'cell where the hyperlink is
Application.Run "'WorkbookName.ext'!YourSubName", Arg1, Arg2
End If
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target2 As Hyperlink)
If Target.Range.Address = "$B$10" Then 'cell where the hyperlink is
Application.Run "'WorkbookName.ext'!YourSubName", Arg1, Arg2
End If
End Sub