PDA

View Full Version : Is there a before_insert_row event?



TrippyTom
08-06-2013, 09:50 PM
Hi everyone,

I have a macro that inserts a row and copies conditional formatting so it doesn't mess up. But some of my users are inserting rows via the shortcut keys. Since this is done the manual way it messes up my conditional formatting because it wasn't done through my macro.

Is there a way I can catch the event BEFORE they insert a row the normal way? I would like to intercept that and have it run my macro instead.

Thanks,
Tom

TrippyTom
08-06-2013, 09:51 PM
Oh I forgot to mention this is a shared file on a network. (I make it unshareable to edit the vba)

SamT
08-07-2013, 01:39 AM
I recently had a memory problem and can't find my comprehensive workbook on command bars, but I think this is how you do it.

These actually replace the the action performed by the Excel Insert Rows menu, then reset it back to normal. Or at least that's what I hope.


Option Explicit

Sub Worksheet_Activate()
CommandBars("Insert").Controls("Rows").OnAction = "Your InsertRow Macro Name"
End Sub

Sub Worksheet_DeActivate()
CommandBars("Insert").Controls("Rows").Reset
End Sub

TrippyTom
08-07-2013, 04:25 AM
Thanks Sam,

I put that code in the worksheet I wanted it to work in and it isn't working when I insert a row either by ALT+I+R, or clicking on the left edge of a row and pressing CTRL+"+" on the numberpad.

Does it matter that I'm in Excel 2010 since that uses the ribbon instead of "commandbars"?

TrippyTom
08-07-2013, 04:27 AM
oops nevermind, it works!!! :) (I forgot to prefex my macro with the name of where the macro is since it's in a different place)
Marking thread solved. Thanks so much Sam!

TrippyTom
08-11-2013, 08:08 AM
OK, it seems I might need more to this. This works with the shortcut: ALT + I + R (insert row), but it doesn't catch when a user selected the entire row and presses the shortcut: CTRL + (numberpad +) to insert a row.

Is there a way to intercept that shortcut as well?

SamT
08-11-2013, 08:56 AM
Google "Command Bar Lister, John Green, CBList"

I use CBList to list all the CommandBars and menus.

I think the object you need to disable is

CommandBars("Built-in Menus").Controls("Insert").Controls("Rows")

However, I am not sure of the Collections in that code. The first Controls collection might be another CommandBars collection. You will need to experiment.