Hyperlinks are only active when

Hyperlinks in the sheet protection do not work
authormessage
Dima25
guest


Posted:
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.
guest



Posted:
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):
Code:
'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.
guest



Posted:
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:
http://www.uploadagent.de/show-187770-1351442637.html

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.
maninweb
Microsoft Excel MVP 2014


Posted:
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.

greeting
_________________
The greatest superstition of the present is the belief in the right of way.
Jacques Tatischeff - also known as Jacques Tati
20FCC4FD0E3240C59522E3F0C4695941
guest



Posted:
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
guest



Posted:
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
Rudi26
Excel formulas: Formulas from Excel 2003 do not work in Excel 200710Chris_HN2716May 10, 2009, 7:11 pm
Chris_HN
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
guest
Excel formulas: Vreference with IF query or how can it work13felixatmell144229 Jan 2009 10:48
felixatmell
Excel formulas: tab jump adjustable for sheet protection?1paper2008152107 Jan 2009, 18:13
neopa
Excel formulas: hyperlinks8SandraZa60306 Nov 2008, 3:06 pm
SandraZa
Excel formulas: problem with hyperlinks within a workbook0jule2008682Jul 21, 2008, 3:56 pm
jule2008
Excel formulas: hyperlinks - reference?6Marcus12345687March 21, 2008, 8:58 pm
Marcus12345
Excel formulas: automatically create hyperlinks2savas82591March 21, 2008, 5:24 pm
urs
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
sisscuss