data validation modification when sheet protected

Question

I have a long data validation list of times by half hours. When the sheet is not protected, the user is able to double click on the time already entered from the list in a data validation cell and modify the time to exact minutes. Even when the data validation cells are not protected, when the sheet is locked, the only option is to delete the cell contents and enter the exact time manually. Is there a workaround to allow the user the simpler option to double click when the sheet is locked?

Answer 1

Hmm, I see no difference in behaviour between the two sheets. In both sheets I can select a time from the dropdown, for example 12:00:00 and manually edit the cell to show 12:03:00. 

That works in Excel Online and in Excel for Windows (2016). I don't have 2007, so maybe it's version specific.

Answer 2

Teylyn, I find that I am able to make direct cell edits in the Onedrive worksheet but not when I open the example workbook in Excel to edit it in Excel 2007 or 2010.  Just to verify that I  understand correctly, you tested the example by opening it to edit outside Onedrive in Excel version 2016?

Your feedback is greatly appreciated.

Answer 3

The example is now doing exactly what I needed but I was unable to determine what the modification was that now allows the double click edit.

Answer 4

The difference is that the protected Sheet2 will not allow the user to double click, bypassing the drop down and allowing a direct edit of data already entered in the cell.  For instance, supervisor schedules employee to begin work at 11:00 AM and enters that time in A2. For labor records, the exact arrival time must be entered when known. The employee clocked in late at 11:06 AM.  On the unlocked Sheet1, you can double click on the cell when the time of 11:00 AM has already been entered from the drop down, place your cursor next to the colon and delete and replace the minutes with the exact arrival time.  In the locked Sheet2, even though A2 is not protected, you cannot but must instead delete the contents of the cell and manually enter the exact time.  I am trying to pass-protect formulas while allowing the easier double click editing.

I hope this clarifies the problem.