Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Determine If Cell Has Hyperlink

  1. #1
    Join Date
    26th May 2007
    Posts
    31

    Determine If Cell Has Hyperlink

    I'm trying to edit hyperlink targets. If run the following test on a cell:
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,706

    Re: Determine If Cell Has Hyperlink

    VB:
    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 
    
    

  3. #3
    Join Date
    26th May 2007
    Posts
    31

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,706

  5. #5
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Determine If Cell Has Hyperlink

    You sure? I just tested this and it worked fine:
    VB:
    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.
    MS MVP - Excel

  6. #6
    Join Date
    26th May 2007
    Posts
    31

    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:

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Determine If Cell Has Hyperlink

    Better solution, thanks for sharing.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Link To Hyperlink Cell & Result In Hyperlink
    By andrew88 in forum EXCEL HELP
    Replies: 1
    Last Post: August 12th, 2008, 10:07
  2. Replies: 5
    Last Post: March 14th, 2008, 23:22
  3. Replies: 1
    Last Post: January 24th, 2008, 06:42
  4. Determine Cell Value Or N/A
    By angela.sanghera in forum EXCEL HELP
    Replies: 2
    Last Post: December 20th, 2007, 22:57
  5. Determine Cell Content By Other Cell Contents
    By Kasturi in forum EXCEL HELP
    Replies: 3
    Last Post: August 15th, 2007, 00:31

Bookmarks

Posting Permissions

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