trapping "insert row" event 1

[AcousticalConsultant]

Hello,

So essentially what I am trying to do it trap an "insert row" event so that I can prevent it on specific lines, but allow it on others. I know that there is no specific event to trap for insert rows, so we have to go about it another way. One way is to monitor the Worksheet_Change event, but that code would run every single time something is changed in the sheet which kind of bugs me. Instead, I found some interesting code to "redirect" the insert row command to a macro. It works fine on my machine, but when I tried running it on some other computers, it crashes. Here is the code, I'll explain the error afterwards:

    Dim MenuBarInsertRow As CommandBarControl
    Dim RightClickInsertRow As CommandBarControl

    Set MenuBarInsertRow = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=296, Recursive:=True)
    Set RightClickInsertRow = Application.CommandBars("Row").FindControl(ID:=3183, Recursive:=True)

    MenuBarInsertRow.OnAction = ThisWorkbook.Name & "!CustomInsertRow"
    RightClickInsertRow.OnAction = ThisWorkbook.Name & "!CustomInsertRow"

Ok, so the "MenuBarInsertRow" object is redirecting the insert row command selected from the menu, and the "RightClickInsertRow" object is redirecting the insert row command selected from the right-click drop-down menu.

The problem is with the right-click control. On some computers, it seems that it can't find the control. So I modified the code to look for it in any command bar as follows:

    Dim MenuBarInsertRow As CommandBarControl
    Dim RightClickInsertRow As CommandBarControl

    Set MenuBarInsertRow = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=296, Recursive:=True)
    
    For Each a In Application.CommandBars
            Set RightClickInsertRow = a.FindControl(ID:=3183, Recursive:=True)
            If Not RightClickInsertRow Is Nothing Then Exit For
    Next a

    MenuBarInsertRow.OnAction = ThisWorkbook.Name & "!CustomInsertRow"
    
    If RightClickInsertRow Is Nothing Then
        MsgBox "Insert row restrictions will not work"
    Else
        RightClickInsertRow.OnAction = ThisWorkbook.Name & "!CustomInsertRow"
    End If

Even with this code, it doesn't find the control and the RightClickInsertRow object is "Nothing". However, it seems that if I go to a sheet, right-click and select insert row - then try the macro again. Now it works! It's as if I need to 'wake-up' the insert row command for the code to find it. What's going on here? And how can I make sure that I can find this command if I'm distributing this code to others?

Sorry for the long-winded question.
I appreciate any help or insight.

Regards,
AC

 

[SkipVought]

hi,

One way is to monitor the Worksheet_Change event, but that code would run every single time something is changed in the sheet which kind of bugs me.

So exactly what is it about running your Worksheet_Change event code that bugs you? Is it degrading performance?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AcousticalConsultant]

Well, that was my initial concern - but it probably won't matter much. So if I were to explore that method, I know that I can track the target range, but how can I trace whether the change was an 'insert row' event?

 

[SkipVought]

this might be a start...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range, i As Integer
    i = 1
    For Each t In Target
        If t.Address <> rSelection(i).Address Then
            MsgBox "INSERT ROW do it and get out"
            Exit For
        End If
        i = i + 1
    Next
End Sub

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AcousticalConsultant]

That helps. Thanks Skip.

It'll require some tweaking to suit my needs, but it's certainly a start, and it'll help make the code more compatible.

 

[SkipVought]

Sorry, my PASTE failed to include

[b]
Dim rSelection as range
[/b]
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range, i As Integer
    i = 1
    For Each t In Target
        If t.Address <> rSelection(i).Address Then
            MsgBox "INSERT ROW do it and get out"
            Exit For
        End If
        i = i + 1
    Next
End Sub

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AcousticalConsultant]

I've been working at creating a solution for my needs, but I've concluding that it's just not working this way. It makes it very difficult when the user selects "undo". If the user undoes an "insert row" event, and the selected cell when the undo is initiated happens to be above the inserted row, then we can't know what has changed on the worksheet. It's very difficult to isolate and differentiate between an "insert" and "delete" event. This is getting to be quite frustrating. What I intially expected to be a fairly easy task is turning out to be very complicated. argh!

Any other ideas out there?
Thanks

 

[SkipVought]

When you short circuit native Excel features, you're on your own. GOOD LUCK!

You could try inserting a hidden sheet that would mirror the sheet of interest. Use the Worksheet_SelectionChange event to populate that sheet. Also store the last X selection addresses (LIFO like a push down stack). You may be able to mimic the undo logic using that combination of data, BUT yer in for some VERY INTENSE and PRECISE logic analysis of this process. "Coder beware!"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AcousticalConsultant]

I think I'd prefer to go back to my initial approach (re-directing the commandbar actions), but I need it to be more robust for distribution on other computers.

Anybody got some ideas?

 

[fumei]

but I need it to be more robust for distribution on other computers."

I think you will be SOL on that one.

Gerry