Validation Needed To Prevent Deletion

[swordswinger710]

In many of the cells where I have formulas entered, I have validated them to allow only whole numbers less than zero to prevent users from accidentally changing or deleting the formulas. This seems to work fine until someone hits delete, which allows the user to erase the formula and get away with it. I cannot use protection because then I can't merge cells, so I'm wondering if it's possible to use something else in the Validation field which won't allow anyone to change or delete the formula I worked so hard to come up with.

[royUK]

You canm't prevent deletion without protection, and you really should not use Merged Cells. They are a feature of Excel that should really be abolished!

[Portuga]

Without protection you wont be able to prevent users deleting cells.

Alternatevelly, you could try creating a macro that would merge selected cells. (Ask the VB experts here)
This macro would unprotect the sheet, merge selected cells and then protect it again.

If protection is deff not an option you could use the track changes function to find these users and a 9mm.

[dominicb]

Good afternoon Juda

John Walkenbach demonstrates a very nifty trick here to catch most instances of naughty users attempting to delete data validation cells :

http://www.j-walk.com/SS/excel/tips/tip98.htm

HTH

DominicB

Edit :

you could use the track changes function to find these users and a 9mm

Ha ha ha!!! Shouldn't really laugh at that should I ...

[Portuga]

Ha ha ha!!! Shouldn't really laugh at that should I ...

No dominicb, as a moderator you should not allow members to have this kind of tomfoolery behaviour

[DCSwearingen]

http://pubs.logicalexpressions.com/P...cle.asp?ID=808

Reading your requirement again, I don't think you are necessarily asking for Validation, but rather ensuring your orginal formulas remain unchanged.

As indicated in previous comments, deleting a row, column, or individual cell will still change the basic spreadsheet.

But, you can enter the following code into the Worksheet module for the sheet you wish to maintain the formulas.

All I did was modify the approach in the above link.

Please Login or Register  to view this content.

Hopefully this will also resolve the second question posed at http://www.excelforum.com/showthread...05732#poststop

[swordswinger710]

Thanks to you all for all your advice. It is greatly appreciated.