Capturing excel insert/delete events.

[Gaurav Nanda]

How can we capture excel insert/delete column/row event?

One way is to use "Sheet_change" event, but how will come to know, what
change caused this event to raise?

 

[OssieMac]

Hello Gaurav,

The following demonstration code example should point you in the right
direction for identifying if rows or columns are inserted/deleted.

Select a cell in Row 1 out of the way of your data and to the right of any
column likely to be used in your application.

Define a name for the cell and call it ColTest. (See Define names in Help if
required)

Identify the column number of the cell and enter that number in the cell.
(Easy way is to insert =Column() then Copy -> Paste Special -> Values over
top of itself. DO NOT leave as a formula.)

Select a cell in column A out of the way of your data and below any row
likely to be used in your application.

Define a name for the cell and call it RowTest.

Enter the row number in the cell.

(For the above do not select the far right column or the last row on the
worksheet or you will not be able to insert any columns or rows because Excel
will not allow cells with data to be pushed off the side or bottom of the
worksheet.)

Copy the below Worksheet_Change code into the VBA editor. (I assume you know
how to do this because you have already mentioned using Change Event.)

Note my comments in the code.

You need to understand that if using UsedRange anywhere in your code then
the above cells will be included and you may have to handle that situation. I
suggest that you do not use cells too far away to the right or below your
data. If using UsedRange in other code think about Resize and reduce it by
one column and one row.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Range("ColTest").Column = _
Range("ColTest").Value Then

'MsgBox only for demo. Normally no code
'required if no Insert/Delete
'MsgBox "No column inserted or deleted"

GoTo TestRows
End If

If Range("ColTest").Column > _
Range("ColTest").Value Then

'Insert you code here in lieu
'of the MsgBox
MsgBox Range("ColTest") _
.Column - Range("ColTest") _
.Value & " Column/s inserted"

GoTo TestRows
End If

If Range("ColTest").Column < _
Range("ColTest").Value Then

'Insert you code here in lieu
'of the MsgBox
MsgBox Range("ColTest") _
.Value - Range("ColTest") _
.Column & " Column/s deleted"

GoTo TestRows
End If

TestRows:

If Range("RowTest").Row = _
Range("RowTest").Value Then

'MsgBox only for demo. Normally no code
'required if no Insert/Delete
'MsgBox "No Row inserted or deleted"

GoTo ReEnableEvents
End If

If Range("RowTest").Row > _
Range("RowTest").Value Then

'Insert you code here in lieu
'of the MsgBox
MsgBox Range("RowTest") _
.Row - Range("RowTest") _
.Value & " Row/s inserted"

GoTo ReEnableEvents
End If

If Range("RowTest").Row < _
Range("RowTest").Value Then

'Insert you code here in lieu
'of the MsgBox
MsgBox Range("RowTest") _
.Value - Range("RowTest") _
.Row & " Row/s deleted"

GoTo ReEnableEvents
End If

ReEnableEvents:
Range("ColTest") = Range("ColTest").Column
Range("RowTest") = Range("RowTest").Row
Application.EnableEvents = True

End Sub

Because you are disabling events, if your code should fail during testing
then events remain turned off until you restart Excel so insert the
forllowing code into the VBA editor and run it if the code fails for any
reason. For convenience you can place it below the above code in the
worksheet module and you can run it from within the VBA editor. (You won't
see anything when it runs but it does turn the events back on)

Sub ReEnableEvents()
Application.EnableEvents = True
End Sub