It might have something to do with Control's ID property?
I'd guess it's the Type property. The autosum is of type
msoControlSplitButtonPopup and that is not listed among the types the
commandbar controls Add method supports per Help. Nevertheless Excel 2003
did support it and Excel 2007 does only on the 'shadow' cell menu. Go
figure.
Thanks for the additional information.
--
Jim
| Yes, however I'm running this code from J-Walk's Power Programming:
|
| Sub AddToShortCut()
| ' Adds a menu item to the Cell shortcut menu
| Dim Bar As CommandBar
| Dim NewControl As CommandBarButton
| DeleteFromShortcut
| Set Bar = CommandBars("Cell")
| Set NewControl = Bar.Controls.Add _
| (Type:=msoControlButton, ID:=1, _
| temporary:=True)
| With NewControl
| .Caption = "Toggle &Word Wrap"
| .OnAction = "ToggleWordWrap"
| .Picture = Application.CommandBars.GetImageMso("WrapText", 16,
| 16)
| .Style = msoButtonIconAndCaption
| End With
| End Sub
|
| And it works. Also, I'm trying the following code:
|
| Public Sub commandBarsTest()
| Dim cbar As Office.CommandBar
| Dim ctrl As Office.CommandBarControl
|
| Set cbar = CommandBars("Cell")
|
| For Each ctrl In cbar.Controls
| Debug.Print ctrl.ID, ctrl.Caption, _
| ctrl.Visible, ctrl.Enabled
| Next ctrl
| End Sub
|
| Both the "Toggle Word Wrap" and AutoSum are listed (after running OP's
| code to include it), but when using the popup in cell only the Toggle
| Word Wrap is there - not the AutoSum.
|
| It might have something to do with Control's ID property? This code
| works, meaning I see "Test" in the in-cell shortcut menu:
|
| Public Sub testControls()
| Dim cbar As Office.CommandBar
| Dim ctrl As Office.CommandBarControl
|
| Set cbar = CommandBars("Cell")
|
| Set ctrl = cbar.Controls.Add(ID:=1)
|
| ctrl.Visible = True
| ctrl.Enabled = True
| ctrl.Caption = "Test"
| End Sub
|
| > Very interesting. It appears that what we see after right-clicking a
cell
| > is not the "cell" commandbar. At least it is not the "cell" commandbar
we
| > can modified with code.
| >
| > After running your code run this:
| >
| > commandbars("cell").ShowPopup
| >
| > You should see AutoSum at the bottom. But notice that this commandbar
has
| > black font, not blue. And that it doesn't have the floatie thing on
top.
| >
| > --
| >
| >
| > | In 2003 I could do the following:
| > |
| > | Application.CommandBars("Cell").Controls.Add
| > | msoControlSplitButtonPopup, 226
| > |
| > | .......and an Autosum control would be created in the Cell menu.
| > |
| > | (I found 226 by recording a macro of changing a menu)
| > |
| > | In 2007 the above line doesn't seem to work. Although it doesn't throw
| > | an error either! ...
| > |
| > | Any help greatly appreciated
| > |
| > | Regards
| > | JasonQ
|
