2

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
TylerH
  • 20,799
  • 66
  • 75
  • 101
ss198811
  • 23
  • 3

3 Answers3

1
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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • @Gary'sStudent - thanks. :) Currently wondering why `If Not IsError(sh.Hyperlink) Then sh.Hyperlink.Delete` throws `error 1004`, when there is no Hyperlink. Any ideas? – Vityata Jun 18 '18 at 14:47
  • @Gary'sStudent - yup, looks like it. All the solution I found so far are with the `On Error Resume Next` and I try to avoid it in general. – Vityata Jun 18 '18 at 14:51
  • This solution would delete all the hyperlinks, so if you want to be selective (e.g. removing from rectangles but leaving circles untouched), this won't work. – z32a7ul Jun 18 '18 at 15:03
  • if you loop over hyperlinks, you can avoid `OnError`; see my answer for some code. – Gary's Student Jun 18 '18 at 15:08
  • @Vityata: I don't think you can always avoid On Error Resume Next in VBA. The creators of the language did not make possible to check if certain objects exist without getting errors. But you should minimize the scope of it. I always keep it part of the same line, which I expect to throw errors. And the general structure of my below solution is quite useful: a = default_value; a = risky_operation(); evaluate( a ); – z32a7ul Jun 18 '18 at 15:08
  • 1
    Let there be no mistake; even though you are using `OnError`, I think your approach is the best approach to achieve the goal. – Gary's Student Jun 18 '18 at 15:11
  • @Gary'sStudent: Nice idea but I think the solution with On Error Resume Next reflects better the way how you think about this task: You delete the shape's hyperlink. Not: You delete hyperlinks on the shape's worksheet which match the criteria that they are the same as the one attached to this shape. – z32a7ul Jun 18 '18 at 15:12
1

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
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

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
z32a7ul
  • 3,695
  • 3
  • 21
  • 45