CODEWITHSUNDEEP
vbaexcel
ss198811
ss198811

Reputation: 23

Check if hyperlink exists in a shape

I have a shape in an Excel sheet, and I have to add/remove hyperlink to it as a part of my code. How can we check if the shape contains a hyperlink? Something like the below code:

if shape.hyperlink.exists is True then
   shape.hyperlink.delete
end if

Upvotes: 2

Views: 2101

Answers (3)

Gary's Student
Gary's Student

Reputation: 96791

It is possible to loop over all the hyperlinks on a sheet and determine whether those hyperlinks are in cells or in Shapes (this avoids using OnError):

Sub HyperActive()
    Dim h As Hyperlink, n As Long

    If ActiveSheet.Hyperlinks.Count = 0 Then
        MsgBox "no hyperlinks"
        Exit Sub
    End If

    For Each h In ActiveSheet.Hyperlinks
        n = h.Type
        If n = 0 Then
            MsgBox "in cell: " & h.Parent.Address
        ElseIf n = 1 Then
            MsgBox "in shape: " & h.Shape.Name
        End If
    Next h
End Sub

Upvotes: 1

z32a7ul
z32a7ul

Reputation: 3797

To check if a Shape has a Hyperlink, call this function (instead of the 'shape.hyperlink.exists') in your post:

Public Function HasHyperlink(shpTarget As Shape) As Boolean
    Dim hLink As Hyperlink: Set hLink = Nothing
    On Error Resume Next: Set hLink = shpTarget.Hyperlink: On Error GoTo 0
    HasHyperlink = Not (hLink Is Nothing)
End Function

Upvotes: 0

Vityata
Vityata

Reputation: 43595

Public Sub TestMe()

    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        On Error Resume Next
        sh.Hyperlink.Delete
        On Error GoTo 0
    Next sh

End Sub

The idea is to delete the hyperlink of every shape. If the shape does not have one, it is quite ok, the code continues. In this solution, the hyperlink is declared as a variable - How do I refer to a shape's hyperlinks using Excel VBA - as a workaround something similar can be used.

Upvotes: 1

Related Questions