Announcement

Collapse
No announcement yet.

Determine If Cell Has Hyperlink

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Determine If Cell Has Hyperlink

    I'm trying to edit hyperlink targets. If run the following test on a cell:
    Code:
    linkAddress = .Cells(xstart, I).Hyperlinks(1).SubAddress
    . . . it delivers the target fine if the cell in question has a hyperlink, but it errors out if the cell doesn't have one.

    How can I test for the existence of a hyperlink within a specific cell first, so I can avoid this error?

    Thanks,

    David

  • #2
    Re: Determine If Cell Has Hyperlink

    Code:
    On Error Resume Next
    linkAddress = vbNullString
    linkAddress = .Cells(xstart, I).Hyperlinks(1).SubAddress
    On Error GoTo 0
    
    If linkAddress = vbNullString Then
       'No Hyperlink code
    Else
        'Hyperlink code
    End If

    Comment


    • #3
      Re: Determine If Cell Has Hyperlink

      Tried that code. It still errors out with:

      Runtime error 438

      Object doesn't support this property or method


      as soon as it hits a cell without a hyperlink, and the "linkAddress = .Cells" line is encountered.

      Comment


      • #4
        Re: Determine If Cell Has Hyperlink

        It works for me just fine.

        Comment


        • #5
          Re: Determine If Cell Has Hyperlink

          You sure? I just tested this and it worked fine:
          Code:
          Sub x()
              Dim c As Range, s As String
              
              On Error Resume Next
              For Each c In ActiveSheet.UsedRange
                  s = c.Hyperlinks(1).SubAddress
                  If Err = 0 Then MsgBox s
                  Err.Clear
              Next
          End Sub
          Entia non sunt multiplicanda sine necessitate.

          Comment


          • #6
            Re: Determine If Cell Has Hyperlink

            Probably my screwup.

            I was experimenting and removed the (1) from the .Hyperlinks, and I'm pretty sure under some conditions this still works, but undoubtedly caused the error to persist under others.

            At any rate, I came up with another somewhat simpler approach. By querying for the number of hyperlinks in a cell:

            Code:
            If .Cells(xstart, I).Hyperlinks.Count > 0 Then
                'Hyperlink code
            End If
            . . . I can confirm whether or not one exists.

            Thanks for all your help.

            Comment


            • #7
              Re: Determine If Cell Has Hyperlink

              Better solution, thanks for sharing.
              Entia non sunt multiplicanda sine necessitate.

              Comment

              Working...
              X