Figuring out hyperlink in a cell with a macro in VBA
 

Hi,
I am in a desperate need of help. Can anyone let me know how to find
out a cell that contains a hyperlink. I mean we can figure out formula
by hasFormula function. How can we find for a hyperlink. Please help,
Nasir.

Figuring out hyperlink in a cell with a macro in VBA
 

Hello Nasir,

There is no built-in Excel worksheet function to test if a cell
contains a hyperlink. The following macro code is for a User Defined
Function that will test a cell for a hyperlink and return either True
or False.

Copy this code, add a VBA module to your Workbook, and paste the code
into the module.

Cell Formula...

=HasHyperlink(B50)

If B50 contains a Hyperlink the cell with the Formula will be TRUE,
otherwise FALSE.


Code:
--------------------
Function HasHyperlink(Cell_Address As Range) As Boolean

Application.Volatile

Dim I As Long
Dim LinkRng As Range
Dim Wks As Worksheet
Dim X As Range

Set Wks = ThisWorkbook.Worksheets(Cell_Address.Parent.Name)
Lnks = Wks.Hyperlinks.Count

If Lnks 0 Then
With Wks.Hyperlinks
Set LinkRng = .Item(1).Range
For I = 1 To Lnks
Set LinkRng = Application.Union(LinkRng, .Item(I).Range)
Next I
End With
End If

Set X = Application.Intersect(Cell_Address, LinkRng)

If X Is Nothing Then
HasHyperlink = False
Else
HasHyperlink = True
End If

End Function

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=553913