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?

 

[tactps]

I hate to see a post unanswered!

I am not aware of anything like this. I had a similar issue and chose to add a button connected to a macro to add rows and perform the other functions.

Maybe our MVP's know more.

Sorry

 

[Smitty]

I'm with tactps.

Or a clever way of sing the existing events?

Not sure what this means though.

As for Class Modules, Juan Pablo Gonzalez has spent a lot of time with them. Might be worth a PM.

HTH,

Smitty

 

[Tom Urtis]

OK, here's something but first some background.

One of the MVPs on this board, and one of the newest Microsoft Excel MVPs, is Masaru Kaji, also known as Colo. Bill Jelen's newest Excel & VBA book will be out in a couple months, and among the contributions to it is from Colo, for code that triggers an event by the act of deleting a row or column.

Your question deals with inserting a row. So, the code below will trigger an event when a row is inserted. All credit goes to Colo; he is the brains behind this; all I am doing is adjusting the code regarding the command bar controls and some of the code to change it from row deletion (his original code) to insertion (your question).

His original code for this is here on his site:
http://www.interq.or.jp/sun/puremis/colo/

What you would do is run the macro he calls "EventHack" to alert Excel that it should alert you when a row is inserted. To disable the code, run the "EventReset" macro. You can run these at Workbook Open, Activate, Close, or Deactivate.

Here is the entire code, full thanks again to Colo, modified very slightly by me to address the issue of row insertion:


Sub EventHack()
AssignMacro "JudgeRng"
End Sub
Sub EventReset()
AssignMacro ""
End Sub

Private Sub AssignMacro(ByVal strProc As String)
Dim lngId As Long
Dim CtrlCbc As CommandBarControl
Dim CtrlCbcRet As CommandBarControls
Dim arrIdNum As Variant

' 295 Insert Cells from worksheet menu
' 296 Insert rows from worksheet menu
' 945 Insert from right click menu

arrIdNum = Array(295, 296, 945)
For lngId = LBound(arrIdNum) To UBound(arrIdNum)
Set CtrlCbcRet = CommandBars.FindControls(ID:=arrIdNum(lngId))
For Each CtrlCbc In CtrlCbcRet
CtrlCbc.OnAction = strProc
Next
Set CtrlCbcRet = Nothing
Next
End Sub

Sub JudgeRng()
If Not TypeOf Selection Is Range Then Exit Sub
With Selection
If .Address = .EntireRow.Address Then
Call InsertRows("Row:" & .Row, xlUp)
Else
Application.Dialogs(xlDialogEditDelete).Show
End If
End With
End Sub

Sub InsertRows(ByVal str, ByVal lngDerec As Long)
MsgBox "Inserted: " & str & vbCrLf & "Your event code would go here."
Selection.Insert lngDerec
End Sub

 

[Legacy 21301]

Although not as sophisicated as Colo's, here's another possibilty.

Add a blank worksheet, name it "Hidden" and hide it.

Put this in the module for your data sheet (assumed to be named "Data") :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rws&
rws = Sheets("Data").Range([A1], ActiveSheet.UsedRange).Rows.Count
With Sheets("Hidden").[A1]
    If rws = .Value Then Exit Sub
    Application.EnableEvents = False
    If rws > .Value Then
        MsgBox rws - .Value & " row(s) have been inserted" 'Put your event code here
    End If
    .Value = rws
    Application.EnableEvents = True
End With
End Sub

 

[CraigM]

Cheers guys, thanks for the help. I'll play around with both, and figure out which suits me best.
Apologies for the typo pennysaver, I meant "using", not "sing".

 

[CraigM]

Just a note for anyone else who sees Tom's very useful code here. I think the line:
arrIdNum = Array(295, 296, 945)
should be replaced with:
arrIdNum = Array(295, 296, 3183)
I believe that the 945 control is that to insert a new sheet, where 3183 is the control to insert a row from the right click menu.

 

[thingone]

I believe that the 945 control is that to insert a new sheet, where 3183 is the control to insert a row from the right click menu.

Could you please provide a link to your source for this information?

 

[thingone]

HUGE PROBLEM caused by the code in post #4.

PROBLEM HISTORY:
After checking the code in post #4 for errors and updating the numerical references shown in the by the array "arrIdNum" to reflect what Microsoft published for Excel 2016's policy id numbers (sometimes called Control ID's) for row addition or deletion from a table or from a sheet, and for table resizing. Here is my source for those ID numbers:
https://www.microsoft.com/en-us/download/details.aspx?id=50745

After the above single modification to the "arrIDNum", I added that modified code (cut and paste sans modification) to the code pane for "ThisWorkbook" in my spreadsheet. I also added "Option Explicit" to the top of this same code pane.

I then checked the code for any errors by using the "Debug" from the Menu Bar of the code editor and selecting "Compile VBA Project". I received one error and corrected it as follows:
Original line of code where the error occurred:

[COLOR=#333333]Set CtrlCbcRet = CommandBars.FindControls(ID:=arrIdNum(lngId))[/COLOR]

Corrected Code:

[COLOR=#333333]Set CtrlCbcRet = Excel.CommandBars.FindControls(ID:=arrIdNum(lngId))[/COLOR]

At this point I followed the following steps:

1. Saved the Workbook.
2. Closed the Workbook.
3. Reopened the Workbook.
4. Selected a row within the only table in the only worksheet within the Workbook.
5. Attempted to delete the selected row.
6. Got the following error message:

Cannot run the macro 'volunteering_timesheet.xlsm!JudgeRng'. The macro may not be available in this workbook or all macros may be disabled.

This was a fair enough warning as I had not yet saved the document as a *.xlsm" (macro embeded) excel file. So I repeated steps 1 this time saving it as an *.xlsm (macro embeded) excel file, and continued through steps 2 through 5 as normal which triggered step 6 all over again.

This time I deleted all the macros in the workbook. Then deleted the workbook itself and reopened the original Non-Macro Enabled excel file. I checked to make sure that it did not have any macro's in it. (It did not). I went through steps 4 and 5 above, and shockingly step 6 was again triggered.

PROBLEM DESCRIPTION:
At this point there is absolutely no way to get rid of this message and no way to delete rows in a table of any Excel file weather or not its newly created or an already existing file.

Questions:

1. I am not the administrator on the computer this happened on. Is there any way to fix this problem without having to do any administrative tasks?

 

[amr4alex]

I had the same problem and this my code to put inside the "sheet class"

Public aa As Range

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not rangeIsBlank(aa) Then
    If aa.Address <> Target.Address Then MsgBox (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