"Insert row" event?

[Tony]

Hi. I have a worksheet where I would like an event macro to be triggered
if someone inserts a row. (The macro goes off and inserts a
corresponding line on another sheet.)

The question is, how do I arrange for the macro to fire under this
precise condition? I've tried using the Change event, and checking that
the target is a 1x256 cell range and that the cells in the range are
empty, but you can fool this by selecting a row and hitting Delete.

It is very important that my macro only runs if a line has really been
inserted!

Is there any foolproof way of detecting this? For example, by inspecting
the Undo history of the application?

Any help much appreciated.

Tony

 

[Guest]

The best idea I have had so far is to set a MARKER (a named range) just after
the last row of data when the workbook is opened. Store its row nujmber in a
global variable. Then, in the Change event for the worksheet in question
(Sheet1, for example), compare the current row of the MARKER cell to its
previous row. This lets you know if any (and how many) rows were added or
deleted.

Data entered without adding or deleting rows doesn't change the position of
the MARKER cell. The potential drawback is that data could be entered in rows
beyond the MARKER cell, if rows "out there" were subsequently added or
deleted, this method would not catch it. I don't know if that's an issue for
your particular woksheet or not. If it is, let me know, and we can address
it. But for now, here is the code to use a MARKER as described:

In a VBA module in your workbook, add:
Global PrevMark As Long

Function FindLastCell(Wksht As Worksheet) As String
'Returns address of last cell used (highest row & col) on specified sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With Wksht
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function

In the ThisWorkbook module of your workbook, add:
Private Sub Workbook_Open()
On Error Resume Next
'Delete the range MARKER on Sheet1 if it exists
Sheets("Sheet1").Name("MARKER").Delete
'Add a new range name MARKER one row below the farthest used row & column on
Sheet1.
Range(FindLastCell(Sheets("Sheet1"))).Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="MARKER", RefersToR1C1:=Selection
'Set an initial value for PrevMark&
PrevMark& = Sheets("Sheet1").Range("MARKER").Row
End Sub

In the code module for the specific worksheet, add:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
'Find out which row MARKER is on now.
x& = ActiveSheet.Range("MARKER").Row - PrevMark&
If x& > 0 Then
MsgBox "Added " & x& & " row(s)"
ElseIf x& < 0 Then
MsgBox "Deleted " & Abs(x&) & " row(s)"
Else
MsgBox "No rows added or deleted"
End If
PrevMark& = ActiveSheet.Range("MARKER").Row
End Sub

I have used Sheet1 throughout. Change it to the correct sheet name.

Hope this helps,

Hutch

 

[Tony]

The best idea I have had so far is to set a MARKER (a named range) just after
the last row of data when the workbook is opened. Store its row nujmber in a
global variable. Then, in the Change event for the worksheet in question
(Sheet1, for example), compare the current row of the MARKER cell to its
previous row. This lets you know if any (and how many) rows were added or
deleted.

Wow... very clever!

One minor problem: Selection has to be on the current sheet, so if the
sheet of interest is not the first one, the sequence that sets the
MARKER to the last cell on the sheet:-

Range(FindLastCell(Sheets("NotSheet1"))).Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="MARKER", RefersToR1C1:=Selection

....sets MARKER to a position on Sheet1, setting you up for a crash when you
try to insert a row on NotSheet1.

This can be corrected by avoiding Selection:

ActiveWorkbook.Names.Add Name:="MARKER", _
RefersToR1C1:=Range(FindLastCell(Sheets("RefData"))).offset(1, 0)

Nonetheless, I am very impressed by your insight into the problem. Thanks!

Tony