0

I have this code to list pictures/textboxes with hyperlinks, but it only lists external hyperlinks, is there a way to change it to include internal links as well?

    For Each wsSheet In wbBook.Worksheets
    
        wsSheet.Activate
        
        For Each Hint In ActiveSheet.Shapes
        On Error Resume Next
        
            Set hl = Hint.Hyperlink
            On Error GoTo 0
            If Not hl Is Nothing Then
               
                Print #intFile, hl.Address
                Set hl = Nothing

            End If
       
        Next

    Next wsSheet
1
  • Can you give an example of what you consider as external or internal link? – Reddy Lutonadio Jan 1 at 14:41
1

You must examine both the hyperlink's Address and SubAddress. Websites use Address and internal links use SubAddress

Here is a little sub that loops over all the Shapes on a single worksheet and determines if the Shape has a link and what its type is:

Sub jhahgdsdsf()
    Dim s As Shape, hy As Hyperlink
    Dim msg As String
    
    For Each s In ActiveSheet.Shapes
        On Error Resume Next
            Set hy = s.Hyperlink
            x = Err.Number
        On Error GoTo 0
        
        If x <> 0 Then
            msg = msg & vbCrLf & s.Name & " " & "has no hyperlink"
        Else
            If hy.Address <> "" Then
                msg = msg & vbCrLf & s.Name & " " & "has hyperlink with address " & hy.Address
            Else
                msg = msg & vbCrLf & s.Name & " " & "has hyperlink with sub-address " & hy.SubAddress
            End If
        End If
    Next s
        
    MsgBox msg
End Sub

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.