Double-Click Hyperlink Issues

mnickel

New Member
Joined
Feb 21, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,321
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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:

1646105186306.png
1646105302313.png


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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,849
Office Version
  1. 2016
Platform
  1. Windows
You can also lock the cell and protect the sheet.
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,321
Office Version
  1. 365
Platform
  1. Windows
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):

1646127620833.png


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

1646127757894.png

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

New Member
Joined
Feb 21, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
@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

Well-known Member
Joined
Jul 11, 2018
Messages
1,321
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,175,610
Messages
5,898,437
Members
434,709
Latest member
Ev1lZer0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top