Hyperlinks: Displaying File Names without the Directory Information

The Question

A recent question to Alan left me wondering how to solve the problem without re-writing a piece of the Excel UI.  George (the reader) wanted to know how to get Excel to show just the name of a file when a hyperlink to the file was added.  My thought was to mimic the current form and include the code to trim off the directory path.

Too much work so I did not submit anything.

The Suggestions

As usual, Alan's readers came up with several ways <link> and he provided them.  There were several solutions:

• Using the HYPERLINK() worksheet function to create the hyperlink and the correct naming from two cells: one with the file name, one with the path.  This works if you're populating the worksheet with new information.
• For cases where the directory/file information is already in the worksheet, there is using the HYPERLINK() function after parsing the file name from the directory information.
• A VBA Sub that "fixes" all of the cells with hyperlinks on the ActiveSheet.

As usual, I learned something from each of these suggestions.

My Dissatisfaction

When I read these they didn't fit what I envisioned.  They were two after-the-fact fixes and one work-around that created extra work for the user.  I wanted something that didn't change or increase the work the user did.  I wanted the flow to be as little changed as possible, and then only if necessary and added value.

The Hint

Then another reader posted a simpler version of the macro.  It was its

simplicity that allowed the answer to shine through.  Instead of 16 lines (from "Sub" to "End Sub", inclusive), eight.  No variables instead of 3.  

And in the middle of it, instead of a While...Wend (old fashioned Do...Loop)  was a single statement that used Mid() and InStrRev() to provide the file name.

...except it was still a "run this when you're done" solution. I'm not casting stones here. I've done plenty too many of them.

But, all that aside, here was an elegant piece of code, sparse and direct, that because of its simplicity I could see where to put it.

The Solution

The place to put it would be in an Change event procedure, either at the worksheet or workbook level.  The code below is in Worksheet_Change() but it could also be in Workbook_SheetChange() if you wanted it to be for all sheets in the workbook.  It just needs to trigger when there is a change that might have introduced a Hyperlink.

How it flows

1. There is a change on a worksheet
2. If there is no Hyperlink at the target, exit or go to the next thing in the module.
3. If the text of the hyperlink and the display text are different, leave it alone and exit the sub because this modification was already done.
4. If they are the same then this is a candidate for change.
5. Turn off the event handlers because the code will trigger this event and the earth spiral would begin.
6. If this is a URL, ask for the website name.
7. If this is not a URL, make the display name the same as the file name.
8. Turn the Event handlers back on.

The code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Hyperlinks.Count > 0 Then
        If Target.Hyperlinks(1).TextToDisplay = Target.Hyperlinks(1).Address Then
            Application.EnableEvents = False
            If InStr(1, Target.Hyperlinks(1).TextToDisplay, "://") = 0 Then
                Target.Hyperlinks(1).TextToDisplay = _
                        Mid(Target.Hyperlinks(1).TextToDisplay, InStrRev(Target.Hyperlinks(1).TextToDisplay, "\") + 1)
            Else
                Target.Hyperlinks(1).TextToDisplay = _
                        InputBox("Enter your name for the web page.", "Hyperlink Display Name", Target.Hyperlinks(1).TextToDisplay)
            End If
            Application.EnableEvents = True
        End If
    End If
End Sub