Protecting a cell, but allowing drop down list to work

Resolved · Medium Priority · Version 2016

Thu 28 Sep 2017, 08:41 Delegate Kay said...

Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

Protecting a cell, but allowing drop down list to work

Hi All

I have a cell that I have protected that contains drop down list coming from a data validation drop list. By protecting the cell, it has locked the drop down list too

I need the user not to be able to modify or overwrite the drop down list options however I need the user to be able to open the drop down and select a value.

Any advice or work around on this would be much appreciated.

Thu 28 Sep 2017, 09:20 Trainer Sarah said...

RE: Protecting a cell, but allowing drop down list to work

Hi Kay

Before selecting the Protect Sheet option in the Review ribbon, right click on your cell and select Format Cells. In the dialog box, choose Protection, then untick the box that says 'Lock'.

Now when you protect your sheet, the user will be able to to look at and select from the dropdown list, but will not be able to override those options because of the data validation.

Let me know if you have any further issues with this.

Kind Regards,

Sarah
Excel Trainer

Thu 28 Sep 2017, 12:11 Delegate Kay said...

RE: Protecting a cell, but allowing drop down list to work

Hi Sarah

Thanks for your reply

I have already tried the above and this does not work for what I am trying to achieve

The cell I am protecting is the cell with the dropdown options hence when it is locked, it also locks the dropdown option.

The purpose is for users to be able to select the dropdown option however not be able to modify/overwrite the drop down options.

If it helps, I can send you a copy of the excel spreadsheet?

Please let me know which email address to forward to

Many thanks

Thu 28 Sep 2017, 13:03 Trainer Sarah said...

RE: Protecting a cell, but allowing drop down list to work

Hi Kay

If you specifically don't want users to edit your data validation list, it could be best to type your criteria straight into the 'source' box in the data validation dialog box. Alternatively, if you have too many criteria to do this, you could store your source list on a separate sheet, then hide that sheet.

The solution I gave you before should've worked, because by unticking the 'locked' option under protection format, you are instructing Excel to allow users to type/edit that cell. The data validation would then restrict users which options they could type, and using either of the methods above you can easily protect the source of your list.

Also maybe check that when you click Protect Cells, ensure you have ticked the 'Select Unlocked Cells' is ticked.

Regards,

Sarah

Thu 5 Oct 2017: Automatically marked as resolved.

Training courses