asked on

ActiveX Listbox Multi Select in Excel 2010

I need to be able to extract the multi selected values from my listbox on my worksheet. When I had it set to single selection I had the linked cell set so it would put the value in a specific cell. But now I need to change this to a frmMultiSelectExtended option. How do I extract the selections and have them be put in column A starting in row 50 and just incrementing from there? No clue how to do this in the code for the Click event of the listbox.

Martin Liss

Execute this after the selections are done. And BTW it's a bad idea to use ActiveX controls on a worksheet.

Private Sub DisplaySelect()

Dim lngIndex As Long
Dim lngRow As Long

lngRow = 50
For lngIndex = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(lngIndex) = True Then
        Cells(lngRow, "A") = ListBox1.List(lngIndex)
        lngRow = lngRow + 1
    End If
Next
End Sub

Open in new window

Lawrence Salvucci

ASKER

Thank you, Why is it a bad idea?

Martin Liss

Because they aren't designed to be used on a worksheet and you can run into problems where the controls shrink or the font size changes. This article talks about it.

Lawrence Salvucci

ASKER

I'm guessing that a form control listbox would work better on a worksheet?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

I'm guessing that a form control listbox would work better on a worksheet?

They have their own problem in that the font is small and it can't be made bigger.

Lawrence Salvucci

ASKER

I'm gonna stay with what I have for now. Thank you for the insight and the help with the code.

Martin Liss

You could try it this way and if it works you don't need mlngRow.

Private Sub ListBox1_Change()
Dim lngLastRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row

If ListBox1.Selected(ListBox1.ListIndex) = True Then
    Cells(lngLastRow + 1, "A") = ListBox1.List(ListBox1.ListIndex)
End If
End Sub

Open in new window

In any case you're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016