adding sub menu on right click

[JeanKim]

Sub Add_Menu ()
     With Application.CommandBars("cell")
          .Reset
          With .Controls.Add (Tpe:=msControlButton, temporary:=False)
               .Caption = "Menu 1"
               .OnAction = "Men1"
          End With
     End with
End Sub

Above is code I used to add menu on right click.
Is it possible to add sub menu using VBA?

 

[bugmonsta]

This will work on a right click... added a faceid for you as well! I have added this to the top of the menu using the before:=1 option to show you that it can be moved in the menu

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

[COLOR="#008080"]  ' Dim ContextMenu As CommandBar[/COLOR] <-not needed but left in the code in case any errors occur and I can turn it back on

   Set ContextMenu = Application.CommandBars("Cell")
   
   
  With Application.CommandBars("cell")
          .Reset
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
                .FaceId = 59
               .Caption = "Menu 1"
               .OnAction = "Men1"
          End With
     End With
End Sub

You could of course move this code to run when the workbook is opened each time...just a thought!

 

[JeanKim]

This will work on a right click... added a faceid for you as well! I have added this to the top of the menu using the before:=1 option to show you that it can be moved in the menu

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

[COLOR=#008080]  ' Dim ContextMenu As CommandBar[/COLOR] <-not needed but left in the code in case any errors occur and I can turn it back on

   Set ContextMenu = Application.CommandBars("Cell")
   
   
  With Application.CommandBars("cell")
          .Reset
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
                .FaceId = 59
               .Caption = "Menu 1"
               .OnAction = "Men1"
          End With
     End With
End Sub

You could of course move this code to run when the workbook is opened each time...just a thought!

Thank you for quick reply.

Now, is it possible to create SUB_Menu? (create black triangle to open sub menu)

 

[bugmonsta]

You can, but its not something I know from the top of my head... have a look at Rons site as this is where I built my menu from:

http://www.rondebruin.nl/win/section2.htm <- General context and QAT examples
http://www.rondebruin.nl/win/s2/win014.htm <- Context menus
http://www.rondebruin.nl/win/s2/win009.htm <- using images/icons