Protected worksheet with specific cells unprotected

[ksmith]

I have a family tree worksheet that I want to share with grandkids.  there is an Instruction sheet that I want to protect, leaving 3 cells (hyperlinks) that I want the user to be able to click on.  Problem is, following MS instructions, I cannot seem to make this work.  I enclosing a file of the area.  I want to unprotect cells 

[#2]

@ksmith

To do that, select any blank cell and press Ctrl+A to select all cells on the Sheet and then press Ctrl+1 to open the Format Cells window and then go to the Protection tab of that window, uncheck the Lock checkbox and then check it again and click OK to finish. Now go to the Review Tab and click on Protect Sheet under Protect group and provide a password, confirm the password in the next window.

So now the hyperlinks will still work but no one can make any changes on the Sheet.

[#3]

This did not work for me. the cell is still locked.

[#4]

Sorry for the wrong reply. My reply should be: All cells are still editable. Nothing is locked. Have tried the above fix over and over. Doesn't work for me. I must be missing something or something may be missing in the instructions.

[#5]

@ksmith

Did you apply the Sheet Protection as the last step?  Locking cells won't do anything unless the Sheet is Protected.

[#6]

Yes I did...and it still did not work. After sheet protection applied, I could enter text or numbers into any of the cells, including those I did not want to protect (the hyperlinks).

[#7]

@ksmith 

Are you sure that you locked the Cells before applying the Sheet Protection?

[#8]

o.k. I must have missed the last "lock" detail. However, how do I just protect ONE section of worksheet, and not the entire sheet?

[#9 · Accepted solution]

@ksmith 

For that, you first need to unlock the entire sheet and then lock the cells you want to protect and protect the worksheet itself as a last step in the end. Please follow the points in the screenshot below...

[#10]

Thank you...I missed this step.. all working fine now.

[#11]

You're welcome @ksmith! Glad it is working as desired now.

[#12]

@Subodh_Tiwari_sktneer 

Hello. I've a similar situation, but another problem has appeared. 

Starting point: 

• Column C containts hyperlinks
• Column C "UNLOCKED" as mentioned by you, all other columns are "LOCKED"
• Protect Sheet

Problem:

• When clicking on the first hyperlink -let's say C19- everything works fine
• When clicking somewhere else on the sheet, the hyperlink from C19 opens again (Hyperlink remains somehow in a cache?)
• Only clicking on another hyperlink -e.g. C23- changes behavior
• But it starts again with the hyperlink from C23, no matter where you click on the protected sheet

Is this behavior comprehensible on your Excel?

You have an idea how to solve it?

•  

[#13]

@GeriD1895 

No, my sheet doesn't have this problem.

Is it possible that you have assigned a hyperlink to the entire sheet?  I am not sure how you can check this but go to INSERT/hyperlink and click it.  you will see a dropdown menu showing all of your hyperlinks.  You may have to delete the C23 link and remake it.  Worth a try.

will work on this and let you know if I have a fix.  Hard to trouble shoot without seeing your sheet.

[#14]

@GeriD1895 

When column C is unlocked and all other columns are locked, you cannot select any other cell on the sheet except the cell in column C i.e. the selection will always remain in column C and since column C contains hyperlinks they open when you click on the sheet. And it seems that when you protected the sheet you didn't allow users to select the locked cells.

One way to resolve this is, when you protect the sheet, allow users to select the locked cells so that the selection on the sheet is not always in column C.

[#15]

Hello @Subodh_Tiwari_sktneer 

This is almost the same what I've thaught initially. And I've tried the following: 

1. UNLOCK column D (because I don't want to give the users the possibility to click in all cells/columns E, F, G...)
   --> same behaviour :(
2. Built a new file with similar columns: 
   Unfortunately I was not able to reproduce the problem 

I will keep on analysing and post results or questions here 

[#16]

Hi @Subodh_Tiwari_sktneer ,

@GeriD1895 and I created a simple table to reproduce the issue:

Used formatting:

- make it a table

- unlock cells in column B

- protect worksheet with following options:

Now, after having done that, when you click on one of the URLs the page opens as it should. In this example I have tried all URLs. So far so good. However, clicking somewhere outside the formatted table (I tried column AJ first) the last clicked URL opens. 

It doesn't seem to work everytime with the same columns. Sometimes URLs open when clicking column U, sometimes URLs open when clicking protected columns in the table. 

When opening the Excel table via webbrowser we did not see this behaviour.

This situation leaves us completely puzzled. 

Any idea?

Thx

Lutz