Search code examples
excelvbalistdynamicribbonx

Custom ribbon / dynamic control list of open workbooks


I am trying to create a dynamic ribbon control showing the list of open workbooks.

I was inspired by code to display the list of sheets of a workbook, which works.

After multiple attempts, my control opens to a list that remains blank.

Xml Code.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="FecXtools" insertBeforeMso="TabHome">
                <group id="Navig" label="Navig">
 <dynamicMenu id="ListeDynamiqueWb"
                    label="Liste Classeurs"
                    getContent="CreationMenuDynamiqueWb"
                    invalidateContentOnDrop="true"
                    size="normal"
                    imageMso="ChartShowData"/>
  </group>
</tab>
        </tabs>
    </ribbon>
</customUI>

VBA Code

Option Explicit

'Callback for ListeDynamique getContent
'Procédure pour construire le menu dynamique
Public Sub CreationMenuDynamiqueWB(ctl As IRibbonControl, ByRef content)
    'ouverture de la balise menu
    content = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">"
    'liste les classeurs ouverts
    content = content & Liste_WB(ActiveWorkbook)
    'fermeture de la balise
    content = content & "</menu>"
End Sub


Private Function Liste_WB(wb As Workbook) As String
    Dim strTemp As String
    'Dim wb As Workbook
    
    ' Insertion d'un titre de menu
    strTemp = "<menuSeparator id=""Classeurs"" title=""Classeurs""/>"

    ' ajoute un bouton dans le menu pour chaque feuille du classeur
    For Each wb In Application.Workbooks
        strTemp = strTemp & _
          "<button " & _
          CreationAttribut("id", "Bt" & wb.Name) & " " & _
          CreationAttribut("label", wb.Name) & " " & _
          CreationAttribut("tag", wb.Name) & " " & _
          CreationAttribut("onAction", "ActivationWB") & "/>"
    Next
    Liste_WB = strTemp
End Function


Sub activateWB(control As IRibbonControl)
    wb(control.Tag).activate
End Sub

Solution

  • Finally, I made 2 workbooks.

    The first one that doesn't work, redacting unnecessary code that doesn't concern the Ribbon, and it still doesn't work.

    The second, by copying part of the Xmsl code from the ribbon, and the VBA code identical to workbook 1, and in the second workbook, it works normally.

    So I guess it's coming from the XLSM code, but I'm not sure what's going on

    I'd like to post both workbooks, but I don't see that option on the stackoverflow site.

    I put the two workbooks on dropbox.

    https://www.dropbox.com/sh/ienihcrlzqw1cda/AADmKcIXGDUjFJhL0-vTessAa?dl=0