Prevent formula deletion without using locked cells and sheet protection

[Richard Buttrey]

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.

[dogberry]

Hi

I read somewhere that you can stop people from changing formula in most instances by making it an array formula is that correct or have I read a load of rubbish.


Chris

[Richard Buttrey]

Hello Chris,

I've certainly not heard of that one. Not quite sure why that would apply since an array formula/function is just a normal standard excel function albeit it with some special properties.

Rgds,

[dogberry]

Hi

Thanks I thought it might be a load of gibberish which is a language I understand perfectly

Chris

[Mase723]

Hi

Is there an option when you adjust a formula theres comes a box where you can choose yes or no?

Renato

[Richard Buttrey]

Hi

Is there an option when you adjust a formula theres comes a box where you can choose yes or no?

Renato

Hello Renato,

Yes, that was covered in my original posting. Uncomment all the five option 1 lines of code and comment out the option 2 line.

[Paul]

If you "array enter" a range of formulas - e.g. you select a range of cells, type a formula into one and then press CTRL+SHIFT+ENTER - you cannot change an individual formula in that range without changing or removing all of them. I've never used that type of entry before, so not sure what use it is.

This does not affect individual cells containing array formulas, of course.

[TMS]

If you intend to enter a formula and then drag it down, you could, instead, select all the cells, enter the formula in the first cell, and commit with Ctrl-Shift-Enter. The formula will be propagated through the range.

At face value, it has the same effect, however, you cannot change an individual cell in the range.


Regards, TMS

[TMS]

Sorry Paul, I may have just said the same thing as you ... just didn't read it the same.

Regards, TMS