Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test if cell contains hyperlink (excel2000+)

    How do I check if a cell contains a hyperlink?? --by value, property, format whatever???
    Sorry for my ignorance
    thanx
    Smbs

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Test if cell contains hyperlink (excel2000+)

    You need a bit of Visual Basic for that. You can write a custom function (in a module):

    Function HasHyperlink(oRange As Range) As Boolean
    HasHyperlink = oRange.Hyperlinks.Count
    End Function

    You can use this in other Visual Basic code:

    If HasHyperlink(Range("A1")) Then
    ...
    End If

    and you can also use it in a formula on a worksheet, for example

    =HasHyperlink(A1)

    Note: if you supply a range instead of a single cell, the function will return True if at least one cell in the range contains a hyperlink.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test if cell contains hyperlink (excel2000+)

    You can use something like:
    If range("a1").Hyperlinks.Count >0 then
    msgbox "cellA1 has hyperlink"
    endif

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if cell contains hyperlink (excel2000+)

    Something similar to this:

    Option Explicit

    Sub test()
    Dim oHpl As Hyperlink
    For Each oHpl In ActiveSheet.Hyperlinks
    MsgBox oHpl.Range.Address
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if cell contains hyperlink (excel2000+)

    Darn, I lost by one or two minutes and posted the wrong answer too! <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> <img src=/S/weep.gif border=0 alt=weep width=21 height=16> <img src=/S/weep.gif border=0 alt=weep width=21 height=16> <img src=/S/weep.gif border=0 alt=weep width=21 height=16> <img src=/S/weep.gif border=0 alt=weep width=21 height=16>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if cell contains hyperlink (excel2000+)

    Thanx guys-- as always u r all geniuses
    Smbs

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Test if cell contains hyperlink (excel2000+)

    Discussion has been continued in <post#=434953>post 434953</post#>. To avoid confusion, this thread is locked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •