Calc: Create menus on the fly

edit

@lonk,

Not certain what your end goal is. When something is selected, what is supposed to happen?

Also, the impression was to modify menus on the fly. This would mean when an action takes place, it is cause to modify a menu. From what is seen on the surface here, macros are not needed to create the menu. You can create using Tools->Customize on Menu tab.

Please add further clarification.

Depending on result wanted you may also consider drop down boxes. See answer by Jim K in this post -> Drop down box and database range question.

In my answer, the link no longer exists but I still have the original sample.

@Ratslinger

Sorry for my short explanation in my question, I would like to create the menu when a specific Calc file starts and delete the menu again when it closes.

From hereunder code, I could add "Food"(with Submenu "European") and "Beverage" (with Submenu "Coffee") after "Help" menu. The target is to assign assign Macros to menu items with club signal. In Sheet1 is the plan that the menus will look like that.

@Ratslinger

Public sString, sString2 as String

Sub Main
    Dim i as Integer
    For i = 1 to 2 
        Select Case i
            Case 1
                sString = "Food"
                sString2 = "European"
            Case 2
                sString = "Beverage"
                sString2 = "Coffee"
        End Select
        Call CreateSavedMenus
    next
End Sub

Function CreatePopupMenu( CommandId, Label, Factory ) as Variant
    Dim aPopupMenu(3) as new com.sun.star.beans.PropertyValue
    aPopupMenu(0).Name = "CommandURL"
    aPopupMenu(0).Value = CommandId
    aPopupMenu(1).Name = "Label"
    aPopupMenu(1).Value = Label
    aPopupMenu(2).Name = "Type"
    aPopupMenu(2).Value = 0
    aPopupMenu(3).Name = "ItemDescriptorContainer"
    aPopupMenu(3).Value = Factory.createInstanceWithContext( GetDefaultContext() )
    CreatePopupMenu = aPopupMenu()
End Function

@Ratslinger

Function CreateMenuItem( Command as String, Label as String ) as Variant
    Dim aMenuItem(2) as new com.sun.star.beans.PropertyValue
    aMenuItem(0).Name = "CommandURL"
    aMenuItem(0).Value = Command
    aMenuItem(1).Name = "Label"
    aMenuItem(1).Value = Label
    aMenuItem(2).Name = "Type"
    aMenuItem(2).Value = 0
    CreateMenuItem = aMenuItem()
End Function

@Ratslinger

Sub CreateSavedMenus
    nItemId        = 900
    nMenuBarPos    = 900
    'Standard menu bar
    sMenuBar = "private:resource/menubar/menubar"
    sMyPopupMenuCmdId = "vnd.openoffice.org:CustomMenu1"
    'Retrieve the module configuration manager from central module configuration manager supplier
    oModuleCfgMgrSupplier = createUnoService("com.sun.star.ui.ModuleUIConfigurationManagerSupplier")
    'Retrieve the module configuration manager with module identifier
    'For Calc Application
    oModuleCfgMgr = oModuleCfgMgrSupplier.getUIConfigurationManager( "com.sun.star.sheet.SpreadsheetDocument" )
    oMenuBarSettings = oModuleCfgMgr.getSettings( sMenuBar, True )
    nCount = oMenuBarSettings.getCount()
' New Popup
        oPopupMenu = CreatePopupMenu( sMyPopupMenuCmdId, sString, oMenuBarSettings )
        oPopupMenuContainer = oPopupMenu(3).Value

@Ratslinger

' New Item in Popup
'       oMenuItem = CreateMenuItem( "vnd.sun.star.script:Standard.MenuSelections.Test?language=Basic&location=document", "Test" )
        oMenuItem = CreateMenuItem( "vnd.sun.star.script:Standard.MenuSelections.European?language=Basic&location=document", sString2 )
        oPopupMenuContainer.insertByIndex( 0, oMenuItem )
' Add Popup to menu
        oMenuBarSettings.insertByIndex( nCount, oPopupMenu )
' This saves Current menu Plus one added item (at end) in menubar
        oModuleCfgMgr.replaceSettings( sMenuBar, oMenuBarSettings)
End Sub

'Sub Test
Sub European
    MsgBox "Test"
End Sub

Just got the message after posting an answer. Using icons was one of the problem areas of menu creation. If a menu item was based upon an uno command icons appeared without a problem provided certain settings were made. User commands presented a bigger problem. Had a little success but the icons would not generate upon menu creation. Could only get a display after successive selections of an item. In your example that will be most difficult.

As mentioned in the answer, there are many problems with direct access to menus and icons is just one of them.

Finally, after re-reading this comment, maybe you don't want the icons to appear after all. Anyway, now you know.

@Ratslinger

Thank you so much for your concern. And all icons (or FaceID) are not necessary.