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


"NickHK" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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/arch...-deleted-rows/

Doug
"Charles Chickering"
wrote
in
message ...
you could loop through all the controls and check their captions
until
you
find the one you want, then get that id...
Function FindId(ByVal sCaption As String, ByVal ComBar As
CommandBar)
As
Long
Dim CBC As CommandBarControl
For Each CBC In ComBar.Controls
If CBC.Caption = sCaption Then FindId = CBC.ID: Exit For
Next
End Function

Sub TestFindId()
MsgBox FindId("&Insert", Application.CommandBars("Row"))
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"Doug Glancy" wrote:

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