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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.