Share via

Form Controls on a Protected Worksheet

Anonymous
2016-08-28T00:20:39+00:00

I have a simple form with a few Dropdown/Comb boxes.  When the form is protected and I try to use the dropdowns, I get a message  "The cell or chart that you are trying to change is protected and therefore read-only."  I have "allowed the users to edit ranges" and those cells are working as expected but the dropdowns don't work.  I have also gone back to the Form Control box on the Protection tab and unchecked the Lock box, still not working.   Can someone please help me with this?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

OssieMac 48,001 Reputation points Volunteer Moderator
2016-08-28T03:55:40+00:00

 I am not sure how to link the Drop down box to a cell, can you help me with that?

I thought that must have already had the DropDown linked to a cell with the problem you had. Have you sorted the problem yet?

To set the linked cell, right click the DropDown combo and select Format control -> Control tab.

The Input range for the list is the first field and the second field is the Cell link. Just click the icon at the right of the Cell link field, select the cell that you want to link, click the icon at the right of the displayed field and then OK.

The linked cell only displays the numeric Index of the selected item; not the actual item value. To get the actual value you need to insert a formula in the cell beside the linked cell and use the Offset function to find the actual value from the Input range. See the screen snippet below where:

A2:A12 is the Input range for the DropDown.

B4 is the linked cell showing the 7th item is the list is selected.

Formula in cell C4 is:   =OFFSET(A2,B4-1,0,1,1)

Note that with the Offset function the number of rows to offset is the number of times you would need to press the Down arrow to get to it from the Reference which is A2 and hence B4-1 because although it is the 7th item in the list, if the selected cell is A2 then the down arrow only needs to be pressed 6 times to get to the 7th cell in the list.

Was this answer helpful?

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. OssieMac 48,001 Reputation points Volunteer Moderator
    2016-08-28T01:31:20+00:00

    Do you have a cell linked to the DropDown. If so, then the cell must be unlocked before protecting the worksheet.

    To unlock the cell:

    • Unprotect the worksheet.
    • Select the linked cell.
    • Select "Format" icon in the Home ribbon. (In the Cells block towards right end of ribbon).
    • In the "Format" dropdown, the "Lock cell" icon toggles locking On and Off. If the icon is surrounded with an orange border then it is locked. If no surround border then it is unlocked. Ensure the cell is unlocked and then re-protect the worksheet.

    Was this answer helpful?

    6 people found this answer helpful.
    0 comments
  2. Anonymous
    2016-08-28T04:16:38+00:00

    I am sorry, yes I I did have it linked.  I was confused, which isn't hard these days!   Thank you so much.  I now understand the problem.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-08-28T02:20:19+00:00

    Thank you for such a quick response.  I am not sure how to link the Drop down box to a cell, can you help me with that?

    Was this answer helpful?

    0 comments