Ask Your Question

Calc: Create menus on the fly

asked 2019-09-16 19:14:33 +0100

lonk gravatar image

updated 2019-09-17 01:27:05 +0100

From the sample of @Ratslinger, I have modified for Calc :

Sub Main
    Call CreateSavedMenus
End Sub
oModuleCfgMgr = oModuleCfgMgrSupplier.getUIConfigurationManager( "" ).
sString = "Food"
oMenuItem = CreateMenuItem( "", "European" )

The menu 'Food' and submenu 'European' show. Can you please teach me to create more menus and submenus ? The sample menu and submenus are :

image description

|Fedora 30 Workstation KDE of Fedora from Spins | LibreOffice |

edit retag flag offensive close merge delete



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.

Ratslinger gravatar imageRatslinger ( 2019-09-16 20:47:15 +0100 )edit

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 gravatar imageRatslinger ( 2019-09-17 01:28:36 +0100 )edit


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.

lonk gravatar imagelonk ( 2019-09-17 06:08:59 +0100 )edit


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
End Sub

Function CreatePopupMenu( CommandId, Label, Factory ) as Variant
    Dim aPopupMenu(3) as new
    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
lonk gravatar imagelonk ( 2019-09-17 06:10:54 +0100 )edit


Function CreateMenuItem( Command as String, Label as String ) as Variant
    Dim aMenuItem(2) as new
    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
lonk gravatar imagelonk ( 2019-09-17 06:12:12 +0100 )edit


Sub CreateSavedMenus
    nItemId        = 900
    nMenuBarPos    = 900
    'Standard menu bar
    sMenuBar = "private:resource/menubar/menubar"
    sMyPopupMenuCmdId = ""
    'Retrieve the module configuration manager from central module configuration manager supplier
    oModuleCfgMgrSupplier = createUnoService("")
    'Retrieve the module configuration manager with module identifier
    'For Calc Application
    oModuleCfgMgr = oModuleCfgMgrSupplier.getUIConfigurationManager( "" )
    oMenuBarSettings = oModuleCfgMgr.getSettings( sMenuBar, True )
    nCount = oMenuBarSettings.getCount()
' New Popup
        oPopupMenu = CreatePopupMenu( sMyPopupMenuCmdId, sString, oMenuBarSettings )
        oPopupMenuContainer = oPopupMenu(3).Value
lonk gravatar imagelonk ( 2019-09-17 06:14:11 +0100 )edit


' New Item in Popup
'       oMenuItem = CreateMenuItem( "", "Test" )
        oMenuItem = CreateMenuItem( "", 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
lonk gravatar imagelonk ( 2019-09-17 06:15:06 +0100 )edit

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 gravatar imageRatslinger ( 2019-09-17 06:17:48 +0100 )edit


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

lonk gravatar imagelonk ( 2019-09-17 06:25:56 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-09-17 06:06:57 +0100

Ratslinger gravatar image

updated 2019-09-17 07:13:32 +0100


I answer this with much trepidation. Not certain, based on limited info in question, that this is what you actually need. There is no mention of how or why you are doing this. Regardless I present some more info here and a sample.

The document you reference was posted over three years ago and I spent many weeks if not months just to get to that point. Had many problems with direct modification of menus, most ending with crashes. The purpose of user menus in this context is to modify the selections based upon something already done by the user - selecting an existing item, changing sheets/forms etc. Depending upon that the menu can add, delete or make unavailable items.

The document also explains many ways for access and your post shows an early method. The sample attached uses one mentioned later in the document after various problems were encountered.

Most of the menu items, sub menus and items in any menu are controlled by indexes. My sample uses hard coded numbers but you can replace these with variables. A sub menu is an offshoot of a menu. You must base its' creation on the previous menu item. So if the menu item is food defined:

'New menu
    oPopupMenu = CreatePopupMenu( stMyPopupMenuCmdId, "Food", oMenuBarSettings )
' New Popup
    oPopupMenuContainer = oPopupMenu(3).Value

and the first item is to be another container for more items, a popup within the popup must be created:

'Popup in Popup
' New Popup
    oPopupMenu2 = CreatePopupMenu( stMyPopupMenuCmdId, "European", oMenuBarSettings )
' Add Popup to menu
    oPopupMenuContainer.insertByIndex( 0, oPopupMenu2 )

From there you can add further popups or actual menu items.

The sample adds two main menu items and a sampling of sub menus and actual menu items which when clicked will display a message box.

Sample ----- MenuSample.ods


To present an example of not using macros to create the menus, have quickly created a sample using Tools->customise...

Sample ------- MenuSampleUsingCustomise.ods

Still contains the macros to display the messages.

edit flag offensive delete link more



The method you used is going to present you with problems. The menu items will appear in all Calc documents. To clear, go to Tools->Customise.. and on menu tab select Reset. That should fix the menu problem. Then examine my sample and use that method to access & modify menus.

The macro can be run from the Open Document event. The menu will reset to original upon closing the document without any input from you. Only in effect while document is open.

Ratslinger gravatar imageRatslinger ( 2019-09-17 06:38:30 +0100 )edit


Going back to your first comment and related to my first comment, you don't need to write any macros to accomplish what you want. You can simply use Tools->Customise... and the Menu tab to add items and even link to your macros. Simply insure that Scope is set for the Document and not LibreOffice.

Will save you all the unnecessary coding and accomplish the same thing.

Sample added to edited answer.

Ratslinger gravatar imageRatslinger ( 2019-09-17 06:59:30 +0100 )edit


Thank you so much for your support and you are my lifesaver upon this matter. "Tools > Customize > Menu " can really be done on a specific LibreOffice Calc file.

lonk gravatar imagelonk ( 2019-09-17 08:37:21 +0100 )edit


If I need to assign Sub to the menu using another caption instead of Sub's name.

For example, from your example .ods file, I'd like to show caption as 'Rare' assigned to Sub BeefRare.

Can you please help me how I could do that ?

lonk gravatar imagelonk ( 2019-10-08 07:36:48 +0100 )edit


Sorry for many comments.

Please forget my previous comment.

I found that, we have to assign with Sub first, then close and save.

We have to come back again and rename from Sub name with any caption we need.

So complicated but better than writing codes with tears.

lonk gravatar imagelonk ( 2019-10-08 12:13:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-09-16 19:14:33 +0100

Seen: 338 times

Last updated: Sep 17 '19