Double-Click Hyperlink Issues

[mnickel]

I have a sheet where I have a string of text in one cell that I am using to create a hyperlink to either a PDF directly or a folder if multiple versions of that PDF exists. I am trying to make this sheet as "foolproof" as possible but am running into issues when double-clicking on the hyperlink. Now, I realize that single-clicking on the hyperlink will work fine but a natural tendency of many users will be to double-click.

So here is the issue I am running into. When the hyperlink directs to a PDF, a double-click works fine. But when a hyperlink directs to a folder, the double-click causes the cell go into edit mode while directing to the folder. Obviously, I don't want any user to be able to edit that formula cell by accident. I realize that can simply protect that formula column, but when I do that, the warning message pops up that I am trying to edit a protected cell (again, only for the hyperlinks that direct to folders).

Would anyone have any idea what would be causing this behavior and if there is a solution to it? This is my current formula in the hyperlink cells:
=IF(AND(NOT(ISBLANK(Text Cell)), FolderExists("Folder Path"&Text Cell)), HYPERLINK("Folder Path"&Text Cell, "SEE "&Text Cell), IF(AND(NOT(ISBLANK(Text Cell)), FileExists("File Path"&Text Cell&".PDF")), HYPERLINK("File Path"&Text Cell&".PDF", "SEE "&Text Cell), HYPERLINK(,"NO DRAWING")))

Thanks

 

[Dan_W]

I tried to recreate the issue you've outlined above, but I couldn't. I found that double-clicking on the empty part of the cell that is hyperlinked will, as you describe, cause Excel to initiate edit mode - but that is standard expected behaviour. When I double clicked on the text part of the hyperlinked cell - for both hyperlinked files and folders - Excel did not initiate edit mode.

I am not aware that this can be 'fixed', but two possible workarounds are: (1) hyperlinking a shape or a textbox and using that in place of a hyperlinked cell; or (2) using VBA to prevent the user inadvertently entering 'edit mode'. For the latter option, this can be accomplished with the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 4 Then Cancel = True
End Sub

This tells Excel to prevent double-clicking (and therefore, entering into edit mode) for the entire stretch of fourth column - ie., all the cells in column D (for example). You can access the VBA Code area by right clicking on the worksheet tab and selecting View Code and then entering the code above into the code pane as shown in the screen captures below:

It is worth noting that if you do this, the workbook must then be saved as macro-enabled (XLSM).

Sorry, I know it's not ideal, but that's about all I can think of that might help.

 

[Jaafar Tribak]

You can also lock the cell and protect the sheet.

 

[Dan_W]

But Jaafar, why would anyone want a simple and effective solution, when they could just opt for my convoluted workaround? ?
You're absolutely right, and thank you.

@mnickel - As Jaafar helpfully points out, you could always just protect the worksheet - this will lock down all cells (that are designated to be locked) from any editing but still allow you to click on/use them if hyperlinked. Essentially, the same as my VBA proposal above, but without the annoying step of using code! It all depends on what it is that you intend for the rest of your worksheet - for example - does it need to be editable? If so, you should know that, by default, all cells of a sheet are designated to be locked down when a sheet is protected, but you can deselect a range of cells from being locked in the first place from the Format Cells window -> Protection tab (see screen capture below):

Or you can permit editable ranges - see Review tab on the ribbon -> Allow Edit Ranges:

I feel like I'm forgetting another method of doing this, but I'm not sure what it is - anyway, let me know if any part of the explanation above doesn't make sense or if you encounter any difficulties and I would be happy to help.

 

[mnickel]

@Dan_W & @Jaafar Tribak - thank you very much for your replies.

@Dan_W interesting that you didn't experience the behavior I described when double-clicking the hyperlinks. Even when I created a brand new sheet with different data and file/folder paths, I still experienced the cell going into edit mode when the folder path hyperlink was double-clicked. Very annoying.

In terms of simply protecting the sheet, I had already attempted that option. I want the sheet to be editable and auto expand when new information is entered and protecting the sheet prevents that from happening. I found a VBA work-around for that but it isn't ideal in terms of user-friendliness so I abandoned it.

I had also tried to implement the double-click handler in the past but could not for the life of me get it to do anything. I revisited it today after reading your replies and realized that I had written the code in a general code module as opposed to in the sheet module, so that was why it wasn't doing anything for me. I'm still pretty new to using VBA in Excel, so I'm still learning all the little intricacies. Grateful that there are people out there like you that are willing to share their knowledge!

 

[Dan_W]

To the extent I do 'know' anything about using Excel, it's purely as a result of making far too many mistakes over far too long a period!
Thank you for the kind feedback. I'm glad that we were able to come up with a working workaround!