MS Office - Charger une liste déroulante depuis une plage Excel

Dans cet article, nous allons voir comment charger très facilement une plage Excel dans un ruban et l'afficher dans une liste déroulante. Si vous n'avez encore jamais construit de ruban personnalisé, commencez par MS Office - Comment créer un ruban dans Excel.

L'idée est de proposer une liste de valeurs dans un ruban sans devoir coder ces valeurs en dur : il suffit de pointer vers une plage, n'importe où dans votre classeur.

J'ai utilisé cette technique dans beaucoup de mes applications Excel (c.-à-d. des fichiers Excel contenant du code VBA).

Pour cet article, nous allons créer une liste de périodes (YYYYMM) dans une feuille de calcul et charger cette liste dans notre ruban personnalisé.

De cette façon, nous pouvons offrir une meilleure expérience utilisateur en proposant une liste et en exécutant, par exemple, une requête vers une base de données pour récupérer les données de cette période (ou tout autre traitement).

Voici le résultat : la liste déroulante chargée directement depuis la plage de la feuille, et la valeur sélectionnée réécrite dans une cellule.

Pourquoi ça fonctionne​

• Les éléments de la liste déroulante proviennent d'une plage nommée (_rngParamsPeriod), pas de valeurs codées en dur dans le XML — modifiez la plage dans la feuille et la liste du ruban suit.
• Un callback VBA (getItemCount, déclaré dans le XML du ruban) fait la lecture ; sélectionner une valeur dans le ruban l'écrit directement dans une cellule nommée (_Period) ailleurs dans le classeur.

C'est parti​

Créez d'abord un classeur vide. Créez ensuite une nouvelle feuille appelée par exemple Params avec une liste de valeurs. Pour cet article, créons une liste de périodes :

Rien de compliqué pour l'instant. Pour rester souple, sélectionnez la plage et nommez-la : _rngParamsPeriod. C'est quand même mieux que de coder en dur une plage comme $A$2:$A$14, non ?

La deuxième chose à faire est de prévoir une cellule dans votre feuille où la valeur sélectionnée sera écrite : quand l'utilisateur choisit une valeur dans la liste, nous allons demander à Excel d'y placer la valeur sélectionnée. Pour cela, cliquez sur la cellule où vous souhaitez voir la période sélectionnée et nommez cette cellule _Period. Sur l'image ci-dessous, je choisis la cellule $C$2 dans la même feuille, mais elle peut se trouver ailleurs.

Il est temps d'enregistrer votre fichier Excel une première fois, disons dans c:\temp\ribbon.xlsx, puis de fermer le classeur.

Ajouter un ruban​

Passons à l'ajout de notre ruban. Pour cela, téléchargez cet outil gratuit : https://bettersolutions.com/vba/ribbon/custom-ui-editor-download.htm. Vous y trouverez un exécutable appelé CustomUIEditor.exe. Double-cliquez dessus pour démarrer l'éditeur puis ouvrez votre fichier c:\temp\ribbon.xlsx :

Cliquez sur le menu Insert puis sélectionnez Office 2010 Custom UI Part.

Collez le XML ci-dessous dans la fenêtre de l'éditeur :

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">?
  <ribbon>
    <tabs>
      <tab id="demoRibbon" label="demo Ribbon" insertBeforeMso="TabHome">?
        <group id="grpParams" label="Parameters">
          <dropDown id="paramsCbxPeriod" label="Period" screentip="Select the period" supertip="Please select the period..." onAction="modToolbar_cbxPeriod.onAction" getSelectedItemID="modToolbar_cbxPeriod.getSelectedItemID" getItemLabel="modToolbar_cbxPeriod.getItemLabel" getItemID="modToolbar_cbxPeriod.getItemID" getItemCount="modToolbar_cbxPeriod.getItemCount"/>?
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Vous obtiendrez ceci :

Enregistrez vos modifications et quittez l'éditeur.

Place à notre code VBA​

Depuis l'explorateur, double-cliquez sur votre fichier c:\temp\ribbon.xlsx pour démarrer Excel et rouvrir le classeur.

Vous obtiendrez un message d'erreur et c'est parfaitement normal : il nous reste du code VBA à ajouter. Cliquez simplement sur Ok.

Appuyez sur ALT-F11 pour ouvrir l'éditeur VBE.

Comme illustré ci-dessous, faites un clic droit sur le projet VBEProject et insérez un nouveau module.

Cela fait, cliquez sur le module ajouté et, en bas à gauche, donnez-lui un nom, par exemple modToolbar_cbxPeriod.

Dans la partie principale de l'écran, à droite, collez ce code :

Option Base 0?
Option Compare Text?

' Name of the range in the workbook, with the list of periods
Private Const cRangeName = "_rngParamsPeriod"?

' When a period will be selected from the ribbon, a "name"
' will be created in the active workbook so, in a cell formula,
' it's possible to retrieve the selected value.
' Formula example :
'     = "Selected period is " & _Period
Private Const cName = "_Period"?

Private sValue As String?

' -------------------------------------------------------------
'
' Initialization, define default value : select the last value
' of the range
'
' -------------------------------------------------------------

Public Sub Initialize()

Dim wLastIndex As Byte?

    ' Get the last value of the range
    wLastIndex = shParams.Range(cRangeName).Rows.Count?
    sValue = shParams.Range(cRangeName).Cells(wLastIndex, 1).Value?

    ' Create / Update the name
    Range(cName).Value = sValue?

End Sub

' -------------------------------------------------------------
'
' Return the selected value to the calling code.
'
' For instance, from within a VBA module just call
'    Msgbox modToolbar_cbxPeriod.GetValue()
'
' -------------------------------------------------------------

Public Function GetValue() As String
    GetValue = sValue
End Function

' -------------------------------------------------------------
'
' Remember the selected value : the user has selected a value from
' the ribbon
'
' -------------------------------------------------------------

Sub onAction(control As IRibbonControl, id As String, index As Integer)?
    sValue = id

    Range(cName).Value = sValue

    ' Optional, inform the user in the Excel's statusbar
    Application.StatusBar = "Set to [" & sValue & "]"
End Sub

' -------------------------------------------------------------
'
' Return the number of entries for the combobox
'
' -------------------------------------------------------------

Sub getItemCount(control As IRibbonControl, ByRef returnedVal)?
    returnedVal = shParams.Range(cRangeName).Rows.Count
End Sub

' -------------------------------------------------------------
'
' Set the ID for each entry (the ID can be different of the displayed caption)
'
' -------------------------------------------------------------

Public Sub getItemID(control As IRibbonControl, index As Integer, ByRef id)
    id = shParams.Range(cRangeName).Cells(index + 1, 1).Value?
End Sub

' -------------------------------------------------------------
'
' Define the label (the caption) of each entry in the list
'
' -------------------------------------------------------------

Public Sub getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = shParams.Range(cRangeName).Cells(index + 1, 1).Value
End Sub

' -------------------------------------------------------------
'
' Get the default value for the combobox
'
' -------------------------------------------------------------

Public Sub getSelectedItemID(control As IRibbonControl, ByRef id)
    id = sValue
End Sub

Nous y sommes presque : il faut donner un nom à la feuille où se trouve la plage. Si vous vous souvenez du début de cet article, nous avons créé la plage dans une feuille appelée Params. Dans l'éditeur VBE, sélectionnez donc la feuille Params comme illustré ci-dessous (voir 1.) et nommez la feuille shParams (voir 2).

Testons notre fonctionnalité : fermez le classeur et rouvrez-le. Cette fois, votre liste est remplie et, en sélectionnant une valeur, celle-ci sera injectée dans votre feuille de calcul, prête à être utilisée — exactement le résultat montré en début d'article.

Conclusion​

Une plage nommée, un peu de XML Custom UI et un module de callback VBA : voilà toute la recette d'une liste déroulante de ruban dont les valeurs ne seront plus jamais codées en dur — modifiez la plage, et tous les classeurs qui utilisent ce ruban suivent. Envie de continuer à jouer avec les rubans ? Jetez un œil à mes autres articles sur le sujet.