Sleeper: Trigger event when rows are inserted or deleted

[mdmackillop]

Hi All
Is there a way to run a macro only when rows are inserted or deleted?
MD

[Richie(UK)]

Hi MD,

The only way that I can think of at the moment is this - replace Excel's built-in menu options for Insert and Delete with your own routines that perform the Insert and Delete and also do whatever it is that you want to do.

I haven't actually tried this - just thinking out loud!

[JKwan]

Given what Richie had suggested, try this link.
http://www.andrewsexceltips.com/tips.htm

Look for June 05 blog

[gsouza]

I believe I got this code from this site once. Place this in a worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Target.EntireRow.Address Then
MsgBox "REMINDER! after DELETING row press button 1"
End If
End Sub

[MWE]

I believe I got this code from this sight once. Place this in a worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Target.EntireRow.Address Then
        MsgBox "REMINDER! after DELETING row press button 1"
    End If
End Sub

This will work for Delete, but not for Insert. For reasons not clear to me, Insertion is not considered a "change".

I had a similar need a few years ago where I wanted to control how/what could be inserted or deleted; users were deleting only portions of rows and columns and messing up the formulas in other areas. Controling delete was pretty easy (using Worksheet_Change) but controling Insert was not. I eventually did something along the lines of what Richie suggests, i.e., wrote substitute routines that were dynamically substituted for the standard routines when the particular application was started. Worked OK.

[mdmackillop]

Thanks all.
I'll give these a try
MD