I’m trying to add a submenu to the menubar, but only for one specific calc document A.ods, and the submenu needs to change depending on which sheet is displayed. This was previously working (about 3 years ago), but no longer works. My current version of LibreOffice is 7.4.7.2.
I added a document specific empty submenu to the menubar using Tools → Customize → Menus. The macro code is then triggered whenever the active sheet switches. It deletes any existing submenu items, and then adds the menu items appropriate to the activated sheet.
When I switch sheets, nothing changes in the A.ods menu (the submenu always remains empty), but surprisingly the updated submenu is displayed in other calc documents, with the updated submenu items. Other documents should not display the submenu.
I also tried removing the document-specific submenu. In this case, when the code is run, the added submenu does not show at all in A.ods, but it does show in other documents.
How can I get the dynamic submenu to show in the A.ods which it belongs to, and how can I prevent it showing in other documents?
A slightly simplified version of my current code is:
Sub SetUpMyMenu(ByVal sheetName As String)
Dim moduleCfgMgrSupplier As Object
Dim moduleCfgMgr As Object
Dim menuBarSettings As Object
Dim popupMenu As Variant
Dim popupMenuContainer As Object
Dim menuItem as Variant
Dim menuBar As String
Dim count As Integer
Dim menuIndex As Integer
Dim i, j, k As Integer
menuBar = "private:resource/menubar/menubar"
moduleCfgMgrSupplier = createUnoService("com.sun.star.ui.ModuleUIConfigurationManagerSupplier")
moduleCfgMgr = moduleCfgMgrSupplier.getUIConfigurationManager("com.sun.star.sheet.SpreadsheetDocument")
menuBarSettings = moduleCfgMgr.getSettings(menuBar, True)
count = menuBarSettings.getCount()
menubarIndex = -1
For i = 0 to count-1
popupMenu() = menuBarSettings.getByIndex(i)
For j = 0 To UBound(popupMenu) - 1
If popupMenu(j).Name = "Label" And VarType(popupMenu(j).Value) = V_STRING Then
If popupMenu(j).Value = "MyMenu" Then
For k = 0 To UBound(popupMenu)
If popupMenu(k).Name = "ItemDescriptorContainer" Then
popupMenuContainer = popupMenu(k).Value
Do While popupMenuContainer.getCount() > 0
popupMenuContainer.removeByIndex(0)
Loop
menubarIndex = i
Exit For
End If
Next
Exit For
End If
End If
Next
Next
If menubarIndex < 0 Then
popupMenu = CreatePopupMenu("vnd.openoffice.org:CustomMenu1", "MyMenu", menuBarSettings)
popupMenuContainer = popupMenu(3).Value
End If
Select Case sheetName
Case "Sheet1"
menuItem = CreateMenuItem("InsertItems", "Insert Entry" )
popupMenuContainer.insertByIndex(popupMenuContainer.Count(), menuItem)
menuItem = CreateMenuItem("InsertMultiItems", "Insert Entries..." )
popupMenuContainer.insertByIndex(popupMenuContainer.Count(), menuItem)
menuItem = CreateMenuItem("DeleteItems", "Delete Current Entries" )
popupMenuContainer.insertByIndex(popupMenuContainer.Count(), menuItem)
Case "Sheet2"
menuItem = CreateMenuItem("AppendPrice", "Add New Price" )
popupMenuContainer.insertByIndex(popupMenuContainer.Count(), menuItem)
menuItem = CreateMenuItem("InsertPartPrice", "Insert Part Price" )
popupMenuContainer.insertByIndex(popupMenuContainer.Count(), menuItem)
menuItem = CreateMenuItem("DeletePrice", "Delete Price" )
popupMenuContainer.insertByIndex(popupMenuContainer.Count(), menuItem)
End Select
If menubarIndex < 0 Then
menuBarSettings.insertByIndex(count, popupMenu)
End If
moduleCfgMgr.replaceSettings(menuBar, menuBarSettings)
End Sub
