• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
Dermot

Code to allow multi select listboxes

Using the Developer ribbon, Excel allows you to add listboxes and even to make them multi-select, but doesn't give you a way of listing the multiple selections in the worksheet.

If you set your listbox up like this
2020-07-25 21_38_27-Format Control.jpg

your cell link will just show 0 - basically the listbox breaks.
(I know the cell link is missing above, but it doesn't make a difference)

The code below will fix this by showing the user selections in the column to the right of the list of items in the worksheet, as shown below. All you do is set the listbox up as shown above (no need to include a cell link), then right click on the box and assign the macro below.

2020-07-25 21_53_14-fredt.xlsb - Excel.jpg


VBA Code:
'gets selected items in a multi select list box and lists them next to the original list of items in the worksheet
Sub getUserSelections()
  Dim V As Variant, V2 As Variant
  Dim i As Long, n As Long, lb As Long, adjust As Long
  With ActiveSheet.ListBoxes(Application.Caller)
    V = .Selected 'get 1-D array of selected status, TRUE/FALSE
    n = UBound(V)
    ReDim V2(n, 1)
    lb = LBound(V2, 2) 'the lower bound will be 0 unless Option base 1 is set
    'if it is zero, then we need to be careful because the list in V has a lower bound of 1
    If lb = 0 Then adjust = 1
    For i = lb To n - adjust
      V2(i, lb) = V(i + adjust)
    Next i
    'paste array
    Range(.ListFillRange).Cells(1, 1).Offset(0, 1).Resize(n, 1) = V2
  End With
Excel Version
365, 2019, 2016, 2013, 2011, 2010, 2007
Author
Dermot
Views
85
First release
Last update
Rating
5.00 star(s) 1 ratings

More Excel articles from Dermot

Some videos you may like

This Week's Hot Topics

Top