ExcelBanter - Adding AUTOSUM to Cell menu in Excel 2007

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Adding AUTOSUM to Cell menu in Excel 2007 (https://www.excelbanter.com/excel-programming/405963-adding-autosum-cell-menu-excel-2007-a.html)

Adding AUTOSUM to Cell menu in Excel 2007

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

Adding AUTOSUM to Cell menu in Excel 2007

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.

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

Adding AUTOSUM to Cell menu in Excel 2007

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

On Feb 12, 11:06 am, "Jim Rech" wrote:
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.

--
Jim"WhytheQ" wrote in message

...
| 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

Adding AUTOSUM to Cell menu in Excel 2007

Adding AUTOSUM to Cell menu in Excel 2007

On 12 Feb, 18:48, "Jim Rech" wrote:
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"ilia" wrote in message

...
| 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
|
| On Feb 12, 11:06 am, "Jim Rech" wrote:
| 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.
|
| --
| Jim"WhytheQ" wrote in message
|
| ....
| | 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
|

Hello Jim

Do you know if it is possible to alter the shadow menu?

Regards
JasonQ.