Row menu Insert control ID changes

[Doug Glancy]

I've had a bug in a program for months now. I've finally figured out the
cause and a solution, but am interested if somebody has a better solution.

It concerns the right-click Row popup menu that comes up when entire rows
are selected. It only occurs at Excel startup, generally by opening a file
from Windows Explorer. What happens is that the Insert control on the Row
menu has an ID of 3181 until the Row menu is activated for the first time.
Once it's activated the ID becomes 3183. The solution I found was to add
and then immediately delete a control from the Row menu.

To see this, add this module to a workbook, save it, close Excel and then
open the file from Windows Explorer. In the immediate window you'll see the
two IDs. This assumes that the Insert control is number 5 on the Row menu -
adjust if it's not.

Private Sub Workbook_Activate()
Rows(1).EntireRow.Select
With Application.CommandBars("Row")
Debug.Print .Controls(5).ID
' this is the fix
.Controls.Add
.Controls(.Controls.Count).Delete
Debug.Print .Controls(5).ID
End With
End Sub

The reason this is a problem for me is I'm hooking controls (as described in
Professional Excel Development and Daily Dose of Excel) when a workbook is
opened. If it's the first workbook opened after Excel starts, then 3183 is
nothing, and is not hooked, and the click event for 3183 does not run.

XL 2003 Win XP

I'd appreciate any thoughts.

Doug

 

[Peter T]

Hi Doug,

I haven't tried your example but when I want to do things in my open event I
know won't work until, for example, there's a visible workbook, I call with
OnTime.

If you've got a successful ref to 3183 in your open event do it now,
otherwise try again OnTime.

BTW, apart from 3183 are id's 3181, 3197 in "Row" & "Column" relevant for
you

Regards,
Peter T

 

[Doug Glancy]

Charles,

Thanks. I used to do something like that until I read the post at the link
below. You'll see that it took me a while to get it, but it's pretty cool.
I don't think your proposal would work, for the same reason I ran into, for
some reason the control has one ID until the menu is accessed once, then it
has another.

http://www.dailydoseofexcel.com/archives/2006/08/21/capture-deleted-rows/

Doug

 

[Doug Glancy]

Thanks Peter.

That's a good point about OnTime, but in this case it doesn't seem to change
until the Row menu is accessed once.

Yes, I've got 4 or 5 different insert row IDs that I'm hooking.

Doug

 

[Peter T]

That's a good point about OnTime, but in this case it doesn't seem to
change

until the Row menu is accessed once.

I'm not sure of the order of things for you but typically calling OnTime in
an Addin's open will only fire once all wb's have loaded with at least one
visible, when you can then do your stuff to activate/deactivate a row etc.

Yes, I've got 4 or 5 different insert row IDs that I'm hooking.

Just in case these include 22 or 6002 watch out for version differences (I'm
guessing you're trying to trap insert/paste menus) , Tom Ogilvy's catch
somewhere in this -
http://tinyurl.com/yhzpbw

Another vaguely related thread
http://tinyurl.com/yetaku

Regards,
Peter T

 

[NickHK]

Doug,
If you are talking about the "&Insert" when right-click on the rows numbers,
an ID=296 works for me, although it return a .Caption for that as "&Rows".
3183 returns the correct .caption of "&Insert", but never actually fires
the click event in the class.
Not sure what's going on here ; some playing with the menu by Excel behind
the scenes ?

NickHK

 

[Doug Glancy]

NickHK,

What I'm doing is writing a new click event that inserts a predefined
formatted row. The bug was that when I first opened Excel and my workbook,
and tried to insert a row using the control and menu you describe, my
formatted row would not be inserted. Since I added the fix described in my
OP, it always works. For some reason on the 2 computers I've tested that
control changes IDs from 3181 right at startup to 3183.

Thanks for your interest.

Doug

 

[NickHK]

For me XL2002:
3181 always errors, at start up and after
3183 errors at start up, but works once that menu has be invoke once
296 always works

Using that class code outlined in your earlier link, 296 works for me to
intercept the Excel's Insert row action.

NickHK

 

[Doug Glancy]

NickHK,

Interesting. I'm also trapping 296, but in XL 03 it applies to the one on
the Insert Menu as well as the one I dropped on the Formatting bar from the
Customize menu, not to the one on the Row menu.

Doug