Whilst reading this extremely useful tip by John Walkenbach - see http://j-walk.com/ss/excel/usertips/tip045.htm for identifying cells with formulae, it set me thinking about the subject of preventing formula deletion.
There is of course the standard Excel Worksheet Protection and cell locking combination, whereby all cells are protected unless they are unlocked when worksheet protection is set.
However I’ve always found this somewhat of a nuisance when non formulae cells which need unlocking are dotted all over the place and not in simple contiguous ranges where they can be unblocked en bloc. And how many times have we found code tripping up when it needs to do something in a protected sheet, necessitating pairs of Sheet.Unprotect & Sheet.Protect code lines to prevent this. It happens to me all the time.
So after some thought I wrote the following code.
First the caveat. This is a VBA procedure so clearly if the user chooses not to enable macros this won't work.
It's currently set to prevent formula deletion without exception.
If it's necessary to warn the user first and then allow formula deletion if required then uncomment the five lines of the second IF..End If section (marked Option 1) and comment out the Option 2 line.
It's a Worksheet_Change event, but equally the same code could be put in a Workbook_SheetChange event where it would kick in on any sheet rather than a specific sheet.
Regards
Please Login or Register to view this content.
Bookmarks