Insert Row "Event"

[CraigM]

Is it possible to somehow create custom events via class modules? What I would like is a "Insert Row Event" which I can use to run some code whenever a row is inserted into a sheet. Anyone know if/how this can be done? Or a clever way of sing the existing events?

 

[amr4alex]

you can also detemine if it is a "column" or "Row" as below code to replace "Worksheet_Change" sub

Public aa As Range
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not rangeIsBlank(aa) Then
    If Target.Rows.Count = Rows.Count Then c_r = "Column"
    If Target.Columns.Count = Columns.Count Then c_r = "Row"
    If aa.Address <> Target.Address Then MsgBox (c_r & "(" & Target.Address & ") Inserted!")
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set aa = Target
End Sub

Private Function rangeIsBlank(ByRef rng As Range) As Boolean
  tmp = ""
  On Error Resume Next
  tmp = rng.Address
  rangeIsBlank = IIf(tmp = "", True, False)
End Function

 

[PDE_Papa]

To anybody who stumbles upon this thread, if you are having the same issue that thingone is having in reply #9, make sure you are not putting the subroutines in a workbook/worksheet object. The subroutines need to be placed in a module.

 

[mikerickson]

This code in a sheet's code module, shows when an entire row is inserted.

Dim lastRow As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xStr As String
    On Error Resume Next
    xStr = lastRow.Address
    If Err = 424 Then
        MsgBox "entire row inserted"
    End If
    On Error GoTo 0
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set lastRow = Cells(Rows.Count, 1).EntireRow
End Sub

The idea is that if an entire row is inserted the old last row no longer exists. It becomes one of those odd objects that doesn't exist, but isn't Nothing either.