Context Menu Customising

[Excel User]

Hi,

I am trying to create a sub menu to the context menu, I have added 2 items
but cannot create a sub menu with other commands within that:


With Application
.CommandBars("Cell").Controls("Item1").Delete
Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
.CommandBars("Cell").Controls("Item2").Delete
Set cBut2 = .CommandBars("Cell").Controls.Add(Temporary:=True)
End With


With cBut
.Caption = "Item1"
.Style = msoButtonCaption
.OnAction = "Item1"
End With

With cBut2
.Caption = "Item2"
.Style = msoButtonCaption
.OnAction = "Item2"
End With


Any pointer would be appreciated.

Thanks

 

[Jim Cone]

You can add submenu items to a control but not to a button...
Dim cBut As CommandBarControl
Dim cBut2 As CommandBarControl
Dim cItem As CommandBarButton

Then add cItem to cBut and cBut2.
--
Jim Cone
Portland, Oregon USA



"Excel User"
<[email protected]>
wrote in message
Hi,
I am trying to create a sub menu to the context menu, I have added 2 items
but cannot create a sub menu with other commands within that:

With Application
.CommandBars("Cell").Controls("Item1").Delete
Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
.CommandBars("Cell").Controls("Item2").Delete
Set cBut2 = .CommandBars("Cell").Controls.Add(Temporary:=True)
End With

With cBut
.Caption = "Item1"
.Style = msoButtonCaption
.OnAction = "Item1"
End With

With cBut2
.Caption = "Item2"
.Style = msoButtonCaption
.OnAction = "Item2"
End With

Any pointer would be appreciated.
Thanks

 

[Excel User]

Jim,

I'm alittle confused,

I would like to show on the menu:

item 1
item 2
Flyout Menu > item 3
item 4


Hope this make sense?

Thanks

 

[Jim Cone]

To add items to the top of the cell menu, add them
in reverse order of appearance - last first.
If you are going to have others using your code then don't delete
any existing menu items. That will help keep you from being hated.

Something like this (untested)...
'--
Sub WhatsOnTheMenu()
Dim cmdItem As CommandBarButton
Dim cmdCtrl As CommandBarControl

With Application.CommandBars("Cell").Controls
'Add the popup (flyover)
Set cmdCtrl = .Add(Type:=msoControlPopup, before:=1)
Set cmdItem = cmdCtrl.Controls.Add(Type:=msoControlButton)
cmdItem.Caption = "Another Warning"
cmdItem.OnAction = ThisWorkbook.Name & "!YouWillBeSorry"
'Add items to the popup
Set cmdItem = cmdCtrl.Controls.Add(Type:=msoControlButton)
cmdItem.Caption = "Final Warning"
cmdItem.OnAction = ThisWorkbook.Name & "!ItWillBlowUp"
'Repeat as desired

'Add the separate menu items 'These will be added above the popup
Set cmdItem = .Add(Type:=msoControlButton, before:=1)
cmdItem.Caption = "Warning"
cmdItem.OnAction = ThisWorkbook.Name & "!DoItAnyway"

Set cmdItem = .Add(Type:=msoControlButton, before:=1)
'and so on
End With
End Sub
'--
A picture of a custom "cell" menu here...
http://i258.photobucket.com/albums/hh247/James_Cone/DetermineColorsPicture.jpg
Download "Determine Colors" (free) from here...
http://excelusergroup.org/media/p/326.aspx

--
Jim Cone
Portland, Oregon USA




"Excel User" <
(e-mail address removed)>
wrote in message
Jim,
I'm alittle confused,
I would like to show on the menu:

item 1
item 2
Flyout Menu > item 3
item 4


Hope this make sense?

Thanks