How to extract a Hyperlink Screen Tip

0

I have an Excel chart whose cells contain hyperlinks. I would like to extract the ScreenTip from the cell. Replacing the cell contents with the value would be fine.

I have created a VBA Function in Excel that extracts the ScreenTip successfully,
with the following exceptions:
  (1) It forces me to enter a cell location. I'd like it to work on the cell I run the
       Function on.
  (2) When I 'slide' the successfully 'extracted' cell to other cells in order to copy the
       function, the contents are always the original 'extracted' cell value, even though
       the Formula shows the updated cell number.

Below is the Function I'm currently using.

Function GetHyperlinkScreenTip(Hyperlink_cell As Range) 
    GetHyperlinkScreenTip = WorksheetFunction.Clean _ 
    (Hyperlink_cell.Hyperlinks(1).ScreenTip) 
End Function 


Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Answers

0

I didn't understand what problem you have after removing the screen tip. Therefore I decided to explain the whole thing to you and you will be able to help yourself. Start with understanding that a hyperliknk has five properties as follows. Anchor (the cell in which it resides), Address (like a workbook), Subaddress (like the sheet and cell in a workbook), TextToDisplay (what to show in the cell where it resides) and ScreenTip. The following code creates a hyperlink in A10 of your active worksheet.

Private Sub SetHyperlink()
    With ActiveSheet
        .Hyperlinks.Add Anchor:=.Cells(10, 1), _
                        Address:=.Parent.Name, _
                        SubAddress:=.Name & "!" & Cells(10, 3).Address, _
                        TextToDisplay:="Link", _
                        ScreenTip:="Test this link"
    End With
End Sub

As you see, the link leads to cell C10 in the active sheet, just 2 columns away from the link itself.

The following procedure lets you read the properties of this or any other hyperlink. Click & Hold on the hyperlink. Then run the code. You will see a message box. If there is no hyperlink in the clicked cell nothing will happen.

Sub DisplayHyperlinkProperties()
    Dim Txt As String
    
    On Error GoTo ErrExit
    With ActiveCell.Hyperlinks(1)
        Txt = "Address = " & .Address & vbCr & _
              "SubAddress = " & .SubAddress & vbCr & _
              "TextToDisplay = " & .TextToDisplay & vbCr & _
              "ScreenTip = " & .ScreenTip
    End With
    MsgBox Txt, vbInformation, "Hyperlink properties"
ErrExit:
End Sub

The next procedure is a little bit more complicated. It allows you to modify any hyperlink but you have to set it up first. Click & Hold on the link and run the code. You can't do that with F5. Please read on for instructions. And you shouldn't do it before you know what to change. Please look at the code.

Sub ModifyHyperlink(Target As Range, _
                    Optional Del As Boolean)
    With Target.Hyperlinks(1)
        If Del Then
            .Delete
'            Target.Value = "No more link"
        Else
'            .Address = ""
'            .SubAddress = ""
'            .TextToDisplay = ""
            .ScreenTip = "New screen tip"
        End If
    End With
End Sub

First, there is the 'Del' argument. If it is omitted in the procedure call if will not be True. But if it is True the hyperlink will be deleted. In that case the cell will still show the TextToDisplay. So, if you un-Rem the line to set the 'Target.Value' you can set any other cell value you want or just "".

I have remmed out all the properties except the ScreenTip. The above code will give a new value to ScreenTip. If you want to remove it set it to "". You seeem to have a problem with the cell Value. If you retain the hyperlink that would be the TextToDisplay, and of course it would copy to other cells. With the above code you can change it while keeping the link or deleting it.

The following code is for calling the 'ModifyHyperlink' procedure. Select & Hold the hyperlink and run this code. It will call the procedure just explained which will work on the hyperlink in the selected cell..

Private Sub Try()
    If ActiveCell.Hyperlinks.Count Then ModifyHyperlink ActiveCell, True
End Sub

All of the above code should be in a standard code module. The next, and last, piece of code should go in the code sheet of the worksheet on which you have the hyperlinks.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Hyperlinks.Count Then
        ModifyHyperlink Target, False
        Cancel = True
    End If
End Sub

This is a more convenient way of calling the procedure 'ModifyHyperlinks'. Click & Hold the hyperlink you want to modify and then double-click. Make sure you have set up the 'ModifyHyperlink' procedure correctly before you do. Observe that a argument (False) is passed to the 'Del' parameter. The code doesn't require it because 'Del' is optional and False by default.

All the above code in also in the attached workbook.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login