- Excel Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
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
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.
If you set your listbox up like this
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.
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
