Creating an Excel hyperlink that can't be deleted

0
votes

I have an Excel spreadsheet that has protected sheets/tabs. These protected sheets/tabs contain hyperlinks in some of the cells. Those hyperlink cells are not locked, meaning users can click on them to make use of the hyperlinks (if those cells were locked, users would not be able to click on them). However, because those cells are not locked in the protected sheets, it means that users can delete the hyperlinks in those cells. Can someone please tell me how I can force a cell to have a permanent hyperlink that CANNOT be edited or modified? I have those hyperlink cells protected with Data Validation, which disables users from MODIFYING the cell hyperlink value....but Data Validation does not prevent them from deleting the hyperlink value all-together. Can someone tell me if there is a fix for this?

1
excelhyperlink
The only was I could duplicate this issue was to deselect Select locked cells on the Protect Sheet dialog. If you check that box, it should allow you to click on the links when they are protected. – Rey Juna

1 Answers

0
votes

I think this may work for you.

You've already created the link but for others...

enter image description here

My example has the link in B4

enter image description here

You could even hide the link by making the text & background the same colors

Now the Dinner & Dessert

  1. Create a macro ( if you can do this in VBA, go ahead, this is just a short cut)
    a. From the Developer tab, click Record Macro. Give the macro a name...

enter image description here

b. Click/launch the hyperlink cell.
c. Stop the macro recording. (if you've clicked anywhere else in the spreadsheet, you'll probably need to edit the macro in VBA to remove the additional "movements".)

enter image description here

  1. Create a button ON TOP of the Hyperlink
    a. From the Developer tab, click the "Insert" drop down and click the Button form control (upper left).

enter image description here

b. With the control's "cross-hairs" draw over the top of the link cell. When you release from "covering the link cell", a popup will appear. Now, select the macro name you entered from step 1a above, and click [OK]. A "Button #" control should now appear over the cell. Right click the button control, "Edit text" to change the appearance of the button text to something helpful.

enter image description here

Now you can click the button, not only is the user none the wiser, the cell/sheet can be locked, and the button still works.

enter image description here

Good Luck...