Code needed to repopulate a cell if it's deleted and show a message box.

Hi guys,

I've got some code that populates a table with data from another table whenever a date is selected from a dropdown list (in cell B9).

I'm trying to add some more functionality so that if someone deletes a cell in the first table, then it will repopulate it and throw a message box saying "Oi!, Stop it!" ... or something a bit more diplomatic :D I can't simply protect and lock the cells as I need to be able to click on a cell to get some other information and RAG status using active cell formula. The second table, which contains all the important formula is protected though.

The sample code shows it working for a single cell, but I need to extend this to a range and it keeps falling over. Any ideas greatly appreciated.

Cheers,

Jed.

Please Login or Register  to view this content.

PolulateSummaryTable:

Please Login or Register  to view this content.

Try :

Please Login or Register  to view this content.

Works like a charm.

Just a couple of questions about how it works:

What does this bit do:

If Not Intersect(Range("A:CV"), Target) Is Nothing Then


Also, I assume this bit:

Target.Value = Target.Offset(58, 0).Value

is simply the cell reference from the second table ie it's 58 rows below the copied cell.

Cheers,

Jed.

It says "If Not Intersect(Range("E6:Q20"), Target) Is Nothing Then"

It means if the cell that's changed is not within range "E6:Q20" then it doesn't fire the code between the "IF / ENDIF" part.

And yes, the target.value = Target.Offset(58, 0).value means that the cell value is put to the cell value 58 rows below it.

Excellent, thanks for the explanation and the working code :D

Ahhh, just noticed a problem. It's fine if I delete a cell, but doesn't work if I overtype with a different value :/ Any suggestions?

Adjust as follows. Your first request was "when someone deletes the value", which is different from "changes" ;-)

Please Login or Register  to view this content.

Aha ... you think that I know what I want... :p

I've changed the code (below) but it seems to be constantly looping ie I can see the calculation message showing in the bottom left of the screen and it keeps flickering...

Please Login or Register  to view this content.

From the Sheet:

Please Login or Register  to view this content.

From the Module:

Please Login or Register  to view this content.

Joseph, you are correct ... that code works perfectly. Cheers