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

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" wrote in message
...
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

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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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


"Doug Glancy" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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