Let user select multiple values in VBA toolbar

Question

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!

toolbar? do you mean ribbon or just a UserForm? – Kazimierz Jawor Aug 12 '13 at 08:30 — Kazimierz Jawor, Aug 12 '13 at 08:30
a ribbon (under Add Ins) – Kris Van den Bergh Aug 12 '13 at 08:40 — Kris Van den Bergh, Aug 12 '13 at 08:40
`msoControlDropdown` isn't suited for multiple selection. – LS_ᴅᴇᴠ Aug 12 '13 at 16:00 — LS_ᴅᴇᴠ, Aug 12 '13 at 16:00
Is there another type that allows for multiple selection? – Kris Van den Bergh Aug 13 '13 at 09:21 — Kris Van den Bergh, Aug 13 '13 at 09:21

score 1 · Answer 1 · answered Jul 07 '17 at 16:08

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>

score 0 · Answer 2 · answered Jan 14 '18 at 05:37

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.

Let user select multiple values in VBA toolbar

2 Answers2