Hi Chip. Orginally the post used arrIdNum = Array(295, 296, 945). However
there wa a follow-up note stating:

"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. "

Thanks

EM

"Chip Pearson" wrote:

From your code, it looks like you need

arrIdNum = Array(295, 296, 945)

I can't find a control with an ID equal to 3183. What control are you
trying to access.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 3 Apr 2009 15:29:01 -0700, ExcelMonkey
wrote:

I am trying to get this code to work in Excel 2007 as per
http://www.mrexcel.com/forum/showthread.php?t=80626

I have placed it in Thisworkbook module. It is failing on the line:
Set CtrlCbcRet = CommandBars.FindControls(ID:=arrIdNum(lngId))

It is giving me a Run Time Error 91

Why is this?

Private Sub Workbook_Open()
EventHack
End Sub

Sub EventHack()
AssignMacro "JudgeRng"
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, 3183)
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