Allow users to write in a protected cell

[Squig]

Hi. My ambition in Excel often surpasses my competence...

I am writing an Excel sheet for others to use. I am using a cell as an input box such that entering a number will generate a pre-written statement into an adjacent cell. The sheet is protected to guard against user error and the statements are collated on a separate sheet. Sometimes users will need the flexibility to write their own statement into the cell rather than relying on the text provided. Is there a way to do this without removing the sheet's protection? TIA

[TMS]

If you are using a formula to generate a pre-written statement, allowing a user to enter their own statement would overwrite that formula. To allow that to happen, you would have to unlock that cell (not unprotect the sheet).

[Squig]

Thank you. Is there a way to override the formula without the user needing to go to the formula bar and delete it? I wondered whether I could include a 'free text' option along with the statements? E.g. if the user writes a '9' in the input cell currently it inputs statement 9 but where, if they input a '50' (or an 'a'), the formula would allow them to write their own statement in the cell.

[TMS]

You can either have a formula in a cell or a text value. If you allow the user to type in the cell, it will overwrite the formula. This is permanent and can only be undone by re-entering the formula. You don't need to delete the formula before typing in the cell. But the outcome is the same . . . text and no formula. Even with an option 9, you would need to overwrite the formula.

[Squig]

Thank you for your answer. If nothing else, I won't spend another few hours trying to achieve the impossible!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

Are you still using Excel 2003? If you have moved on, please update your profile.