How to disable direct cell editing by typing, but allow formulas?

**matkiros** · 02-09-2012, 10:53 PM

I'm trying to disable typing directly into a cell, but I'd like to let it evaluate its formula which is based on a cell beside it. How do I do this? I tried using data protection but that doesn't allow evaluating formulas. Using 2003 version thanks

**smuzoen** · 02-10-2012, 01:55 AM

1. Select all cells on worksheet and right click and select format
2. Go to the Protection Tab and unclick Locked
3. Select the cell you want to protect and right click and select format
4. Go to Protection Tab and Tick Locked
5. Go to Tools, Protection, Protect Sheet
6. Enter password to protect sheet ( and you will be prompted to enter again)
Now you can change any cell on the sheet except the cell which you locked. Even if it is a formula based on a cell next to it the formula will recalculate.
By protecting the sheet you will be able to change any cell except the one you locked(i.e the one containing the formula). If there are multiple cells that need to be locked select them all and make them locked cells.
or
You could use a worksheet event - place this into the worksheet with the cell you want to protect

Please Login or Register  to view this content.

Where G10 is an example representing where the formula is

**matkiros** · 02-10-2012, 03:10 AM

Thanks buddy, that does the job for now. I'd like to know though if it's possible to remove even the "unprotect sheet" mechanism from the solution, because ideally there shouldn't be a need for anyone to enter a password to enable direct editing of those cells again?

**smuzoen** · 02-10-2012, 07:04 AM

The easiest way to prevent the user from selecting the cell is to
1. Place the formula into the cell you want to protect e.g. G11
2. Place the following code into the worksheet containing the cell you want to protect - Hit ALT F11 to open the VB editor and double click the worksheet (in the left hand column - e.g. Sheet1, Sheet2 etc) containing the formula and copy an paste the following code into the editor window

Please Login or Register  to view this content.

3. Close the VB editor
With this method the user is never able to select the cell and therefore cannot change the formula. This is the only way without use Sheet Protection.