Solved in cell drop down not working if sheet protected

November 14, 2013 at 07:17:57
Specs: Windows 7
I would like to be able to use the in cell data validation functionality in a cell when the sheet that cell is on is protected. The cell with data validation is unlocked and the sheet it is on is protected abd attempting to click on the cell gives me the usual 'cant do this when the sheet is protected error).

Simply unprotecting the sheet means is works but I would like to be able to protect the sheet and only have this cell locked.


See More: in cell drop down not working if sheet protected

Report •


✔ Best Answer
November 14, 2013 at 11:25:37
Have you tried this in another workbook?

I'm not seeing that behavior in a 2010 workbook. I can't try it in 2013 until I get home tonight.

Here's what I did in 2010:

1 - Sheet1!A1:A5: Entered 1 ,2 3, 4 5
2 - Sheet2!A1: Added a Drop Down referring to Sheet1!A1:A5
3 - Hid Sheet1
4 - Formatted Sheet2!A1 as Unlocked
5 - With Sheet2!A1 selected, Protected Sheet2, Allow user to Select Locked and Unlocked Cells
6 - Accessed Drop Down and selected a value from the list
7 - Unprotected Sheet2
8 - With Sheet2!C1 selected, Protected Sheet2, Allow user to Select Locked and Unlocked Cells
9 - Accessed Drop Down and selected a value from the list

I see no difference - and wouldn't expect to - whether the Unlocked cell is selected or not.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
November 14, 2013 at 09:03:09
The cell with data validation is unlocked

Make sure the cell that the drop down references is also unlocked.

See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
November 14, 2013 at 09:19:57
There should be no need to unlock the cells that provide the data for the drop down list. Having to unlock those cells would mean that users could change the list. That's not usually something that you would want to have happen in a protected sheet.

re: "attempting to click on the cell gives me the usual 'cant do this when the sheet is protected error. "

Do you mean that attempting to use the drop down presents the error or does just clicking on the cell give you the error?

The only cell you should have to unlock is the one with the drop down. It works fine for me, so without seeing your sheet, it's hard to say why it is not working for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
November 14, 2013 at 09:21:18
Thanks for the response. No the referenced cells are no locked. They are however on a hidden worksheet but it makes no difference whether the sheet is hidden or not.

Report •

Related Solutions

#4
November 14, 2013 at 09:23:08
Hi, yes only the cell with the drop down is unlocked, all other cells on the sheet are locked and the sheet is protected. simply clicking on the cell (which is an in-cell drop down via data validation to a column reference lookup on a hidden sheet) poses the error.

It's Excel 2013 by the way if that makes any difference.


Report •

#5
November 14, 2013 at 09:36:48
To clarify it is when clicking in the cell, not when clicking the drop down list

Report •

#6
November 14, 2013 at 10:20:12
More information: If when I protect the sheet, this cell is my current cell, then it seems to work. However if the unlocked cell is not my current sell on the worksheet when I protect it, it never works. Strange!

Report •

#7
November 14, 2013 at 11:25:37
✔ Best Answer
Have you tried this in another workbook?

I'm not seeing that behavior in a 2010 workbook. I can't try it in 2013 until I get home tonight.

Here's what I did in 2010:

1 - Sheet1!A1:A5: Entered 1 ,2 3, 4 5
2 - Sheet2!A1: Added a Drop Down referring to Sheet1!A1:A5
3 - Hid Sheet1
4 - Formatted Sheet2!A1 as Unlocked
5 - With Sheet2!A1 selected, Protected Sheet2, Allow user to Select Locked and Unlocked Cells
6 - Accessed Drop Down and selected a value from the list
7 - Unprotected Sheet2
8 - With Sheet2!C1 selected, Protected Sheet2, Allow user to Select Locked and Unlocked Cells
9 - Accessed Drop Down and selected a value from the list

I see no difference - and wouldn't expect to - whether the Unlocked cell is selected or not.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
November 14, 2013 at 11:31:27
Thanks DerbyDad03 for putting some time into this.... Stupid error on my part in not allowing the user to select the unlocked cell! This was the cause of the problem and working through your example as light bulb came on. Many thanks. Marked as best answer.

Report •

#9
Report •

Ask Question