How to add a dynamic menu to Excel's Ribbon

 excel - Let user select multiple values in VBA toolbar - Stack Overflow

Is there a way in VBA to add a ListBox to a toolbar (Add In ribbon)? I want to be able to let the user select multiple values/criteria in a listbox instead of just one value in a dropdown/combobox menu.

The below code adds a dropdown menu only

Sub addSelectControls()
    Dim newBar As Office.CommandBar
    Set newBar = CommandBars.Add(Name:="testing CommandBar", temporary:=True)
    Dim newCombo As Office.CommandBarComboBox
    Set newCombo = newBar.Controls.Add(Type:=msoControlDropdown, temporary:=True)
    With newCombo

        .AddItem "Blocks"
        .AddItem "Hardware"
        .AddItem "Aircraft Hardware"
        .AddItem "Vehical Hardware"
        .AddItem "Machinery"
        .AddItem "Wood Products"
        .AddItem "Miscellaneous Products"
        .AddItem "Miscellaneous Metal"
        .AddItem "Precast Metal"
        .AddItem "Forged Metal"
        .AddItem "Structural Steel"
        .AddItem "Fabricated Steel"
        .AddItem "Prebent Steel"
        .AddItem "Stock Steel"
        .ListIndex = 13
        .Width = 200
        .Caption = "Category"
        .Style = msoComboLabel
        .BeginGroup = True
        .OnAction = "Category_Select"

    End With
    'ctlComboBoxHandler.SyncBox newCombo
    newBar.Visible = True
End Sub

Please advice. In case you know a better way to do this, that would be great too!

• excel
• vba
• combobox
• listbox
• ribbonx

Share

Improve this question

edited Jul 7, 2017 at 16:09

vacip

5,33633 gold badges2828 silver badges5555 bronze badges

asked Aug 12, 2013 at 8:01

Kris Van den Bergh

1,12244 gold badges1818 silver badges3232 bronze badges

• toolbar? do you mean ribbon or just a UserForm? 

  – Kazimierz Jawor

   Aug 12, 2013 at 8:30
• a ribbon (under Add Ins) 

  – Kris Van den Bergh

   Aug 12, 2013 at 8:40
• 1
  msoControlDropdown isn't suited for multiple selection. 

  – LS_ᴅᴇᴠ

   Aug 12, 2013 at 16:00
• 1
  Is there another type that allows for multiple selection? 

  – Kris Van den Bergh

   Aug 13, 2013 at 9:21

Add a comment

2 Answers

Sorted by:

                                              Highest score (default)                                                                   Trending (recent votes count more)                                                                   Date modified (newest first)                                                                   Date created (oldest first)                              

1

Age-old question, but there is a way to get multiselect dropdown in the Office ribbon. Not too fancy though, as after every selection, you will have to reopen the dropdown. 

You need to use a DynamicMenu with Checkboxes in it.

Here is the XML I have used in the custom ribbon UI thingy:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon>
    <tabs>
      <tab id="mso_c1.FD142A8" label="Diaverum" insertBeforeQ="TabHome">
        <group id="mso_c2.FD142A8" label="Szurés" imageMso="FilterByResource">
          <dynamicMenu id="FilterMenu" label="Beosztás szerint" getContent="GetMenuContent_FilterMenu" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

The GetMenuContent_FilterMenu callback populates the dynamic menu with checkboxes. It basically creates an XML string that contains the commands to create the checkboxes. I use a Table in Excel to get the values from, but you can change this. The code for the callback:

Sub GetMenuContent_Beosztas(control As IRibbonControl, ByRef returnedVal)

Dim CheckboxesXML As String
Dim myCell As Range
Dim i As Integer

For Each myCell In Range("tblBeosztas").Cells
  CheckboxesXML = CheckboxesXML & "<checkBox id=""chk_" & i & """ label=""" & myCell.Value & """ />" & vbNewLine
  i = i + 1
Next myCell

returnedVal = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & vbNewLine & _
    CheckboxesXML & _
    "</menu>"

End Sub

The generated XML looks like this:

<menu xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<checkBox id="chk_0" label="Nővér" />
<checkBox id="chk_1" label="Dializáló asszisztens" />
<checkBox id="chk_2" label="Technikus" />
<checkBox id="chk_4" label="Orvos igazgató" /> 
</menu>

Share

Improve this answer

answered Jul 7, 2017 at 16:08

vacip

5,33633 gold badges2828 silver badges5555 bronze badges

Add a comment

0

Another alternative that I've used recently is just have a ribbon button that pops open a user form with a multi-select listbox. The reason for this approach is that my users had to select something like 8-10 options and opening it each time to select was painful.

The good thing about this approach is that the selection form was only available via that button, and the form was accessible across the application code... so the selection was always check-able even if not displayed. Re-opening it still had the previous selection as well.

I positioned the form at the top of the area just under the ribbon, and added this code to the form:

Public Sub reposition(ByVal aintX As Integer, ByVal aintY As Integer)
    Me.StartUpPosition = 0
    Me.Left = aintX
    Me.Top = aintY
End Sub

Setting the actual X and Y to use based on the position of the button, though, is something I've not figured out how to do, since there doesn't appear to be any way to get that information from a ribbon. I cheated by grabbing a screenshot of my ribbon, and then just counting pixels (OK, I pasted the screenshot into Paint and then drew a box to get the count!) and then subtracted the width of the form from that, and then added the screen offset of the Left of the window, etc. It worked enough to satisfy my users.