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

My apologies, I missed the link and then replied to the wrong post.

https://1drv.ms/x/s!AonLMoGRyMCGaZt1dLhiRYjVkdA

Michael

Answer 3

My apologies, I missed the link.

https://1drv.ms/x/s!AonLMoGRyMCGaZt1dLhiRYjVkdA

Michael

Answer 4

Thank you, Teylyn. Following is an example. You will see in Sheet1 that cell A2 uses the data validation list immediately below it. Sheet1 is unprotected. Double click on A2 and the data already selected from the list may be modified. Sheet2 is exactly the same except locked. Sheet2 A2 is not protected but will not allow the same method of modification.

Michael

Answer 5

Hello, can you post a sample file that shows that behaviour? If data validation is set up to allow only values from a list, how is it possible that users can change the values manually to the exact minute?

You can upload a sample file to your OneDrive, share it and post a link here. You can access your OneDrive with the same account you use for this forum.