Hyperlinks are only active when

Hyperlinks in the sheet protection do not work

Oct 26, 2012, 9:36 am
First name:

       Version: Office 2010

Hello Excel checker,

I have the following problem:
There are several hyperlinks on "Sheet1" (text link in cell), which refer to other tabs (Sheet2, 3,) in the workbook.
When activating the sheet protection (do not select locked / unlocked cells), the links no longer work EXCEPT, the cell with the link was activated when the sheet protection was set:

Sub Macro1 ()
'Select cell with link:
Range ("A1"). Select
'Activate sheet protection:
ActiveSheet.Protect DrawingObjects: = True, Contents: = True, Scenarios: = True
ActiveSheet.EnableSelection = xlNoSelection
End Sub

With this method, however, you can only make one link per tab functional.
With this, Excel proves that sheet protection would have to work without selectable cells with hyperlinks!

However, my wish would be that all links work and that nothing can be selected on the tab.
I have already scoured various forums and unfortunately found nothing suitable.
I also don't want to create buttons and buttons!

I hope you can help me with this problem.

Oct 27, 2012, 11:11 am
First name:

       Version: Office 2010

That should actually work like this and without a range ("A1"). Select work (it worked for me):
'Activate sheet protection:
ActiveSheet.Protect DrawingObjects: = True, Contents: = True, Scenarios: = True, AllowInsertingHyperlinks: = True
ActiveSheet.EnableSelection = xlNoSelection

The macro recorder helps with something like that!
So start recorder, set sheet protection (including setting parameters), stop recorder, look in the VB editor.

Greetings Sven.

Oct 28, 2012, 6:49 pm
First name:

       Version: Office 2010

Hello Sven,

"That should work", but it doesn't work for me.
Maybe I'm doing something wrong.
I also used the macro recorder (I would never get all the commands on my own)

Can you upload your file or an example with?

Here is an example file from me:

File: mappe1.xlsm

Can someone help me after all?

So as I said:
No cells may be selectable, but the links work.
And I don't want buttons or buttons either.
Microsoft Excel MVP 2014

Oct 28, 2012, 7:37 pm
Invocation name: maninweb
Place of residence: Aachen
       Version: Office 2010

Hello guest ...

To my knowledge there is no method that hyperlinks via a click on a locked cell
active when the protection "Select locked cells" is not active.

The case you are describing is likely due to the fact that the cell is actually active (if you have the
If the protection is canceled, the cell is active again). Actually, it shouldn't be.

But you can create a shape (rectangle) and hyperlink it and
Format the shape as invisible = without frame and background color. Then go too
the hyperlinks. You can place the shape exactly above the original link.

The greatest superstition of the present is the belief in the right of way.
Jacques Tatischeff - also known as Jacques Tati

Oct 28, 2012, 7:55 pm
First name:

       Version: Office 2010

Ah OK, you and maniweb are right, that really doesn't work if you don't allow the marking of blocked cells.
I probably hadn't tested that recently. I think I had unlocked the cell with the LINK beforehand. Sorry

Oct 28, 2012, 11:21 pm
First name:

       Version: Office 2010

Hello maninweb,

Thank you for your answer.
Making buttons without borders would have been my next step, but I wanted to avoid that if possible.
It is a pity that it is just an Excel error and cannot be set for all cells.
Maybe you could still do something with VBA (so that the cells for excel look active, but cannot be clicked), but I'm too inexperienced for that.
    UpAll times are
GMT + 1 hour
You can Post in this forum.
You can Reply to posts in this forum.
You can your posts in this forum Not to edit.
You can your posts in this forum Not Clear.
You can to polls in this forum Not participate.
You can Files in this forum Not Post
You can Download files in this forum

Related topics
Forum / Topics reply author Calls last contribution
Excel formulas: delete multiple links and hyperlinks at once14Joken74714057Apr 26, 2010, 1:14 pm
Excel formulas: Formulas from Excel 2003 do not work in Excel 200710Chris_HN2716May 10, 2009, 7:11 pm
Excel formulas: Visual Basic error message when sheet protection is activated17flower fairy3131Apr 11, 2009, 10:38 pm
flower fairy
Excel formulas: Release of hyperlinks only for certain users0guest700Feb 27, 2009, 3:25 pm
Excel formulas: Vreference with IF query or how can it work13felixatmell144229 Jan 2009 10:48
Excel formulas: tab jump adjustable for sheet protection?1paper2008152107 Jan 2009, 18:13
Excel formulas: hyperlinks8SandraZa60306 Nov 2008, 3:06 pm
Excel formulas: problem with hyperlinks within a workbook0jule2008682Jul 21, 2008, 3:56 pm
Excel formulas: hyperlinks - reference?6Marcus12345687March 21, 2008, 8:58 pm
Excel formulas: automatically create hyperlinks2savas82591March 21, 2008, 5:24 pm
Excel formulas: auto filter and sorting despite sheet protection7Blicki1929Jun 28, 2007, 8:48 am
Deep Purple
Excel formulas: automatically increment hyperlinks13sisscuss1218May 04, 2007, 1:16 pm