Protecting HYPERLINK cell disables the link

[hlpmc]

My sheet contains a cell with a HYPERLINK function to direct the user to
a web page URL that is dynamically constructed according to the
contents of cells in other sheets.

I would like the user to be able to click on the cell to follow (not
edit) the hyperlink, to protect the formulas in the sheet, and not to
allow the user to select other cells in the sheet.

When I protect the sheet with the hyperlink cell locked, clicking on
the hyperlink cell has no effect.

When I protect the sheet with the hyperlink cell unlocked, firstly this
is seen as an error by Excel since there's a formula in the cell.
Secondly if I ignore that error, and protect the sheet so as to allow
selection of unlocked cells, clicking anywhere in the sheet causes the
hyperlink to be followed (which is not what I want).

It seems that protecting a hyperlink against editing has the
side-effect of preventing the user following the link to its target.

If this wasn't a dynamically-generated hyperlink, I could achieve the
desired effect by creating a rectangle and adding a fixed hyperlink to
that, then protecting the sheet.

Thanks for any suggestions.

- harry

 

[Dave Peterson]

I just tried this with xl2002 and I couldn't duplicate your results.

I put a link in A1 and put =hyperlink(a1) in another cell. I protected the
worksheet and clicked on the cell with the =hyperlink(a1) and off I went to the
web.

You may want to post back with the version of excel you're using.

===
If there really is a difference in versions, maybe you can drop that rectangle
over the cell (use the alt-key to get it to snap-to the cell's edges) and assign
this macro to the shape:

Option Explicit
Sub myClick()
ActiveWorkbook.FollowHyperlink _
Address:=ActiveSheet.Shapes(Application.Caller).TopLeftCell.Value
End Sub

===
Ps. I tried toggling the Tools|options|Edit tab|edit directly in cell
and no matter what I did, it always followed the link.