Excel error message "The cell or chart you're trying to change is on a protected sheet."

Let me know if you need me to upload a file to see for yourself what I'll describe below.

I have an Excel file with many of the cells locked and the sheet is password protected.  For example, I have a column of unlocked cells that users can enter numbers into.  At the bottom of the column is a simple SUM formula that adds up the cells above it.  The cell that the SUM formula is in is locked.  The data in that cell will change based upon changes users make to the numbers in the cells above...as it should. No problem.

I have another cell that, based upon what value a user selects from a combobox on the sheet, will display a message if a certain value is selected.  I locked the cell that the message displays in because it has a formula in it (the formula is what causes the message to display under certain conditions) that I don't want users to be able to change.

When I protect the sheet and make a selection from the combobox that SHOULD display the message per my formula, I get the following error: "The cell or chart you're trying to change is on a protected sheet."

Any way to correct this?  It seems the use of a combo box is what's causing the problem since protected cells with math formulas such as the one I described initially above allow changes no problem.
Who is Participating?
Ejgil HedegaardCommented:
A combobox has an input range, and a linked cell with the index number of the selected item in the combobox.
You can select an item in the combobox, and the index number change, or you can change the index number in the linked cell, and the item in the combobox change.
Selecting in the combobox writes a value to the linked cell, just like when you type in it, so it can't be protected.
If there is a formula, it will be replaced with the index value.

You can have a formula in the linked cell to change the item in the combobox, and then the cell can be protected, but then you can't use the combobox to select, because the cell is protected.

Formulas can be in protected cells, so make the cell link to another sheet, and let a formula display according to the index in the linked cell.
Then hide the sheet with the linked cell.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.