Calc: Create menus on the fly

[lonk]

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

Sub Main
    Call CreateSavedMenus
End Sub
.
.
oModuleCfgMgr = oModuleCfgMgrSupplier.getUIConfigurationManager( "com.sun.star.sheet.SpreadsheetDocument" ).
.
sString = "Food"
.
oMenuItem = CreateMenuItem( "vnd.sun.star.script:Standard.MenuSelections.European?language=Basic&location=document", "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 :

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

[Ratslinger]

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

[Ratslinger]

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.

[lonk]

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

[lonk]

@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

[lonk]

@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

[lonk]

@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

[lonk]

@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

[Ratslinger]

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.

[lonk]

@Ratslinger

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

[Ratslinger]

Hello,

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

Edit:

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.

[Ratslinger]

@Lonk,

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]

@lonk,

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.

[lonk]

@Ratslinger

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]

@Ratslinger

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]

@Ratslinger

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.