custom ribbon / dynamic control list of opened workbooks

Question

Good morning,

I am trying to create a dynamic ribbon control showing the list of open workbooks. For this I was inspired by the code allowing to display the list of sheets of a workbook, which works. However, 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

=====

I would be very grateful if you could help me figure out what is wrong.

Many thanks in advance.

Philippe Muniesa

Answer 1

activateWB()

To activate the workbook selected from the dynamic menu, use the Activate method of the Workbook object...

Sub activateWB(control As IRibbonControl)
    Workbooks(control.Tag).Activate
End Sub

Inclusion of CreationAttribut()

If you haven't already done so, make sure that you include CreationAttribut()...

Function CreationAttribut(strAttribut As String, Donnee As String) As String
    CreationAttribut = strAttribut & "=" & Chr(34) & Donnee & Chr(34)
End Function

Liste_WB()

First, there's no need to pass the active workbook to Liste_WB. So we can get rid of the parameter in its signature. Secondly, you've mis-spelled the macro being assigned to your buttons. It should be activateWB, not activationWB.

Private Function Liste_WB() 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", "activateWB") & "/>"
    Next
    Liste_WB = strTemp
End Function

If you want to exclude the workbook running the code from the menu, try the following instead...

Private Function Liste_WB() 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
        If wb.Name <> ThisWorkbook.Name Then
            strTemp = strTemp & _
                "<button " & _
                CreationAttribut("id", "Bt" & wb.Name) & " " & _
                CreationAttribut("label", wb.Name) & " " & _
                CreationAttribut("tag", wb.Name) & " " & _
                CreationAttribut("onAction", "activateWB") & "/>"
        End If
    Next
    Liste_WB = strTemp
End Function

VBA Callback for CreationMenuDynamiqueWB()

Call Liste_WB() without passing an argument...

'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()
    'fermeture de la balise
    content = content & "</menu>"
End Sub

Answer 2

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