adding to Context Menus in Excel 2007

[hickymanz]

Hi there
We've just upgraded to Office 2007, and Id created some handy chart tools
for my team. Obviously these didnt work first off and had to do a bit of
fiddling to get these to work again!

strangely i thought that problem would have been harder than getting the
context menu items to appear!

In 2003 I was able to Add items to the RMB context menus for Object/Plot and
Plot Area and they would appear without issue.

in 2007 These get added although they arent visible, i can see them when I
run a script to look at all the context menus, but they never appear with the
RMB!
I've not managed to get an answer as to why I can add Context Menu items for
cells but not Charts!

The Script for adding them I used was

With Application.CommandBars("Object/Plot").Controls
With .Add
.Caption = "Chart Tidy"
.OnAction = "BarChartTools.ChartTidy"
.BeginGroup = True
End With

End With


With Application.CommandBars("Plot Area").Controls
With .Add
.Caption = "Chart Tidy"
.OnAction = "BarChartTools.ChartTidy"
.BeginGroup = True
End With

End With

 

[Jim Rech]

I've seen this before with some other context menu, although I don't
remember which one. I concluded unhappily then that apparently, in some
cases, the context menus we now get are new ones that do not seem to be in
the commandbars collection. So we cannot access them programmatically. How
much does that suck if true? Hope I'm missing something.

--
Jim
| Hi there
| We've just upgraded to Office 2007, and Id created some handy chart tools
| for my team. Obviously these didnt work first off and had to do a bit of
| fiddling to get these to work again!
|
| strangely i thought that problem would have been harder than getting the
| context menu items to appear!
|
| In 2003 I was able to Add items to the RMB context menus for Object/Plot
and
| Plot Area and they would appear without issue.
|
| in 2007 These get added although they arent visible, i can see them when I
| run a script to look at all the context menus, but they never appear with
the
| RMB!
| I've not managed to get an answer as to why I can add Context Menu items
for
| cells but not Charts!
|
| The Script for adding them I used was
|
| With Application.CommandBars("Object/Plot").Controls
| With .Add
| .Caption = "Chart Tidy"
| .OnAction = "BarChartTools.ChartTidy"
| .BeginGroup = True
| End With
|
| End With
|
|
| With Application.CommandBars("Plot Area").Controls
| With .Add
| .Caption = "Chart Tidy"
| .OnAction = "BarChartTools.ChartTidy"
| .BeginGroup = True
| End With
|
| End With
|

 

[hickymanz]

Jim I really really hope you are wrong.
The whole point of these context menus is to make life easier and so users
don't have to remember some shortcut key combo to run a macro.

Hmm I had noticed about the available commands and the ones in the
collection and I was painstakingly going through random context menus in the
hope there would be some light at the end of this tunnel and alas I fall here
too. Although i feel I dont want to give up on this yet as no one has
actually said yes or no, it just seems to be assumptions.....

so theres still hope ... right.....?

 

[Jim Rech]

so theres still hope ... right.....?

I'm not so sure. Look at the code below. It goes through every control on
every commandbar, looking for a control with the caption specified in the
Const. When the const is set to your custom control or to a common one like
"Cu&t" it returns plenty of hits. But when the const looks for an item that
appears on the right-click popup on a chart like "Reset to M&atch Style" it
doesn't find anything. That is why I suspect some popups are not in the
Commandbars collection.

Option Compare Text

Const FindStr As String = "Reset to M&atch Style"
'Const FindStr As String = "Chart Tidy"

Sub a()
Dim cb As CommandBar
Dim Ctrl As CommandBarControl
For Each cb In CommandBars
For Each Ctrl In cb.Controls
If InStr(1, Ctrl.Caption, FindStr) > 0 Then
MsgBox cb.Name & " - " & Ctrl.Caption
End If
Next
Next
End Sub

--
Jim
| Jim I really really hope you are wrong.
| The whole point of these context menus is to make life easier and so users
| don't have to remember some shortcut key combo to run a macro.
|
| Hmm I had noticed about the available commands and the ones in the
| collection and I was painstakingly going through random context menus in
the
| hope there would be some light at the end of this tunnel and alas I fall
here
| too. Although i feel I dont want to give up on this yet as no one has
| actually said yes or no, it just seems to be assumptions.....
|
| so theres still hope ... right.....?
|
| "Jim Rech" wrote:
|
| > I've seen this before with some other context menu, although I don't
| > remember which one. I concluded unhappily then that apparently, in some
| > cases, the context menus we now get are new ones that do not seem to be
in
| > the commandbars collection. So we cannot access them programmatically.
How
| > much does that suck if true? Hope I'm missing something.
| >
| > --
| > Jim

 

[2007FanFoe]

Unfortunately Office 2007 has a new and boring behavior of context
menu in its new objects. Context menu exists in new commandbars
objects but as a sketch, they are always cleaned and restructured on
the fly, then any button you put there Excel will clean it before it
put its buttons. It’s a shame.

 

[hickymanz]

That is intresting, any ideas how it could be possible to stop this behaviour?
Id like my context menus to stick like they do, when I add the context menu
for "Cells"

It seems that Microsoft have limited the ability of power user's
customization, and its very frustrating

 

[Charlotte E.]

Ribbons and all the other restrictions of 2007 sucks!

Untill Microsoft is gonna build in an option, for the user to select for
himself if he want to use the good old menus or the ribbons, do as the rest
of us: Stick to 2003!

 

[hickymanz]

I've got to say I dont have any problem with the new look, I do however feel
that limiting the customization was a bad idea... im sure my opinion will
change more and more as I find that I can't do other things I am used to in
Excel.

Im still Exploring it and trying to run some training courses for some of my
users (especially the ones new to "using" excel) and in some cases its that
little bit easier to teach. But i havent started with the relatively
experienced users yet!

The picture im getting from all of this is that there is "no way" using
excel to stop the lack of customization in Excel context menus etc.
What about other avenues?