VBA to trigger an event on Insert Rows

[KP_SoCal]

I’m using Excel 2007, and I would like to trigger the sub routine listed below when a user attempts to insert a row(s). I’m guessing it would have to be fired on a “Worksheet Change” event. So the logic might go something like, after rows are inserted then Application.Undo (this would undo the rows that were just manually inserted by using the Insert Command on the Ribbon or from right-clicking with the mouse and the cursor would return to where the end user attempted to manually insert the rows) and then the following sub routine would fire…<o></o>
<o> </o>
Sub InsertRow()
Dim Rng, n As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
Selection.EntireRow.Insert
End Sub<o></o>
<o> </o>
Thanks in advanced for any suggestions! <o></o>

 

[jbeaucaire]

Working really hard there...wouldn't it be simplest to go into the OPTIONS for that macro you've already created and assign it a shortcut key...then show the shortcut to your users? To be unable to insert a row the normal way you seems unfriendly.

 

[mikerickson]

I agree that that is not a kind function to link to an "InsertRow" command.
But, to have an Insert or Delete Row Event, one could use the Calculate event.
If the formula =ROWS(1:65536) is in a remote cell, inserting or deleting a row will trigger the Calculate event.
This Calcuate event will detect the insertion or deletion of a row (above the max limit set in the first line)

Private Sub Worksheet_Calculate()
    Const maxRow As Long = 50000
    Dim myCell As Range
    
    On Error Resume Next
        Set myCell = Range("mySecretNamedRange")
    On Error GoTo 0
    
    If Not myCell Is Nothing Then
        Select Case myCell.Row
            Case Is < maxRow
                MsgBox "Row(s) were deleted"
            Case Is = maxRow
                Rem no rows inserted or deleted
            Case Is > maxRow
                MsgBox "Row(s) were inserted"
        End Select
    End If
    
    Rows(maxRow).Name = "mySecretNamedRange"
    ThisWorkbook.Names("mySecretNamedRange").Visible = False
    
End Sub

 

[KP_SoCal]

Hi Mike, your suggestion is really close to what I need to accomplish. I modified it slightly (see colored font). However I'm getting a couple of errors.

1st Error:
After the InsertRow routine is fired, I get an error message after I click [OK] on the input box. The error message states:
Run-time error '1004':
Method 'Undo' of object '_Application' failed

2nd Error:
After the InsertRow routine is fired, I get an error message if I click [Cancel] on the input box. The error message states:
Run-time error '13':
Type mismatch

Any suggestions? Thanks!


Private Sub Worksheet_Calculate()
Const maxRow As Long = 50000
Dim myCell As Range
'Dim Rng, n As Long
On Error Resume Next
Set myCell = Range("mySecretNamedRange")
On Error GoTo 0
If Not myCell Is Nothing Then
Select Case myCell.Row
Case Is < maxRow
MsgBox "Row(s) were deleted"
Case Is = maxRow
Rem no rows inserted or deleted
Case Is > maxRow
Rem MsgBox "Row(s) were inserted"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Call InsertRow
End Select
End If
Rows(maxRow).Name = "mySecretNamedRange"
ThisWorkbook.Names("mySecretNamedRange").Visible = False
End Sub

Private Sub InsertRow()
Dim Rng, n As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
Selection.EntireRow.Insert
End Sub

 

[mikerickson]

Apparently, there is enough code in there that the UnDo option is not avaliable (as is normal with VB routines). Since the user has already inserted a Row, why not leave it there and either ask "insert how many additional rows?" or subtract 1 from their answer to "Insert how many total rows".

About the type mismatch error. Using an InputBox (rather than Application.InputBox), pressing Cancel returns vbNullString.
vbNullString -1 results in a type mismatch error.

This modification:
1)Asks for Additional rows (including the row just entered)
2)Cancel results in the inserted row (which triggered the routine) to be deleted
3)

Private Sub InsertRow()
    Dim Rng as Long, n As Long
    Application.ScreenUpdating = False
    Rng = Application.InputBox("Enter Additional number of rows required.", type:=1)
    Select Case Rng
        Case Is < 1
            Rem cancel pressed
            Selection.EntireRow.Delete
        Case Else
            Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 2, 0)).EntireRow.Insert shift:=xlDown
     End Select
End Sub

I still don't see the utility of this routine.
The number of rows to be inserted can be determined by the mouse before using Excel's insert. IMO, that's easier than filling in an Input Box.
PLUS, there is no UnDo once this routine is run, the UnDo resets.
I wouldn't use this kind of routine.

 

[KP_SoCal]

Thank you for trying to help me understand what was causing the errors. I now understand the conflict that exists between trying to merge the two events into one. I ended creating a command button to run the block of code that I needed.