Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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

Row menu Insert control ID changes
 

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