Protecting HYPERLINK cell disables the link


H

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
 
Ad

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top