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.
--
Gary's Student

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
--
AP

"Gary''s Student" a écrit dans le
message de news: ...
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.
--
Gary's Student

Thank you very much.

It works perfectly.
--
Gary's Student


"Ardus Petus" wrote:

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
--
AP

"Gary''s Student" a écrit dans le
message de news: ...
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.
--
Gary's Student

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
"Gary''s Student" wrote in message
...
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.
--
Gary's Student

Thanks Mike

Your code detects inserted hyperlinks, but not hyperlinks implemented by a
function call.
--
Gary''s Student


"Mike Fogleman" wrote:

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
"Gary''s Student" wrote in message
...
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.
--
Gary's Student

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(".



Gary''s Student wrote:

Thanks Mike

Your code detects inserted hyperlinks, but not hyperlinks implemented by a
function call.
--
Gary''s Student

"Mike Fogleman" wrote:

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
"Gary''s Student" wrote in message
...
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.
--
Gary's Student




--

Dave Peterson

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?
--
Gary''s Student


"Dave Peterson" wrote:

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(".



Gary''s Student wrote:

Thanks Mike

Your code detects inserted hyperlinks, but not hyperlinks implemented by a
function call.
--
Gary''s Student

"Mike Fogleman" wrote:

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
"Gary''s Student" wrote in message
...
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.
--
Gary's Student




--

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))




Gary''s Student wrote:

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?
--
Gary''s Student

"Dave Peterson" wrote:

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(".



Gary''s Student wrote:

Thanks Mike

Your code detects inserted hyperlinks, but not hyperlinks implemented by a
function call.
--
Gary''s Student

"Mike Fogleman" wrote:

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
"Gary''s Student" wrote in message
...
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.
--
Gary's Student




--

Dave Peterson


--

Dave Peterson

Thanks again Dave.
--
Gary's Student


"Dave Peterson" wrote:

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))




Gary''s Student wrote:

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?
--
Gary''s Student

"Dave Peterson" wrote:

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(".



Gary''s Student wrote:

Thanks Mike

Your code detects inserted hyperlinks, but not hyperlinks implemented by a
function call.
--
Gary''s Student

"Mike Fogleman" wrote:

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
"Gary''s Student" wrote in message
...
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.
--
Gary's Student




--

Dave Peterson


--

Dave Peterson

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")
--
Jeff

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"
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")
--
Jeff

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

--
Jeff :-)


"Jim Cone" wrote:

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"
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")
--
Jeff

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"
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

--
Jeff :-)