Hyperlink Screen Tip

Question

I'm using the code below to create a list of filenames from a given folder.

Public Sub ListFilesInFolder(SourceFolder As Scripting.folder, IncludeSubfolders As Boolean)

    Dim fName As String
    Dim Lastrow As Long

    On Error Resume Next

    For Each FileItem In SourceFolder.Files
' display file properties
        Cells(iRow, 3).Formula = FileItem.Name
        Cells(iRow, 4).Formula = FileItem.Path
        iRow = iRow + 1 ' next row number

    Next FileItem

    Range("C17").CurrentRegion.Select
    Selection.Sort Key1:=Range("C17"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    With ActiveSheet
        Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        Lastrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With

        If IncludeSubfolders Then
            For Each SubFolder In SourceFolder.SubFolders
                ListFilesInFolder SubFolder, True
                Next SubFolder
            End If
            Set FileItem = Nothing
            Set SourceFolder = Nothing
            Set FSO = Nothing

    For iRow = 17 To Lastrow
        Cells(iRow, 2).Formula = iRow - 16
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 2), Address:="", _
                                   TextToDisplay:=CStr(iRow - 16)
    Next

End Sub

• Column B contains a unique incremental ID formatted as a hyperlink.
• Column C Contains the file names, and
• Column D contains the file path

When I hover over the hyperlink the 'Screen tip' which appears is the file path, but I'd like to change this so that the 'Screen tip' is the incremental unique ID.

I've read numerous posts about this and tried to change both the 'Text to display' and the 'Screen tip' text but I've been unsuccessful.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to change this.

Answer 1

From a quick play around it seems as though you need to be using ScreenTip rather than TextToDisplay if your goal is the hover over text.

For the section below:

ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 2), Address:="", _
                           TextToDisplay:=CStr(iRow - 16)

Try changing it to:

Activesheet.Hyperlinks.Add Anchor:=Cells(iRow, 2), Address:="", _
                           ScreenTip:=CStr(iRow - 16)

For a test, make a blank spreadsheet and type testvalue in A1. The following will add the hover text as required:

ActiveSheet.Hyperlinks.Add Anchor:=Cells(1, 1), Address:="", ScreenTip:=CStr(Cells(1, 1).Value)

The following will change the text in the cell and won't affect the hover text:

ActiveSheet.Hyperlinks.Add Anchor:=Cells(1, 1), Address:="", TextToDisplay:=Cstr("texthaschanged")