VBA multiselect listbox, populate column with selected items

Question

I have a multiselect listbox populated with unique city names from another sheet. It only has one column of data in the list. It is populated with the code below which was developed with some assistance from some lovely folks on stack exchange.

From this listbox I would like to take the items selected by the user to populate column a in the same sheet. I'm pretty sure this is only a couple of lines of code but I am not sure how to go about it, I have not had any success in counting the items selected in the list.

Any help much appreciated.

Cheers

Sub FilterUniqueData_multi()
    Dim Lrow As Long, test As New Collection
    Dim Value As Variant, temp() As Variant
    ReDim temp(0)
    Dim Value1 As Variant
    Dim endrow As Long


    On Error Resume Next
      Set Billed_sheet = Workbooks("Billed_customers.xlsx").Sheets("Non Household Metered Users")
      With Billed_sheet
                'clear formatting to get rid of merging
                .Range("a:v").ClearFormats
                endrow = .Range("a" & .Rows.count).End(xlUp).Row
               .Range("A2:v" & endrow).Sort _
                Key1:=.Range("h2"), Order1:=xlAscending 'essential to qualify the range on both lines with '.'
                temp = .Range("h2:h" & endrow).Value
       End With


        For Each Value In temp
            If Len(Value) > 0 Then test.Add Value, CStr(Value)
        Next Value

            ReDim temp(0)
            Workbooks("DMA_metered_tool_v4.xlsm").Worksheets("DMA list").Shapes("DMA_listbox").ControlFormat.RemoveAllItems

            For Each Value In test
                Worksheets("DMA list").Shapes("DMA_listbox").ControlFormat.AddItem Value
            Next Value

            Set test = Nothing
            Worksheets("DMA list").Shapes("DMA_listbox").ControlFormat.MultiSelect = xlSimple

End Sub

Rory · Accepted Answer · 2015-03-23 10:33:59Z

1

You need to iterate the items in the list, check the .Selected property and then output that list value if appropriate:

Sub Outputdata()
    Dim wsList                As Worksheet
    Dim lb                    As ListBox
    Dim n                     As Long

    Set wsList = Workbooks("DMA_metered_tool_v4.xlsm").Worksheets("DMA list")
    Set lb = wsList.ListBoxes("DMA_listbox")

    For n = 1 To lb.ListCount
        If lb.Selected(n) Then
            wsList.Cells(wsList.Rows.Count, "A").End(xlUp).Offset(1).Value = lb.List(n)
        End If
    Next n
End Sub

answered Mar 23, 2015 at 10:33

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

squar_o Over a year ago

Cheers @Rory. This works a treat in step through mode, but in runtime it produces duplicates in column A. There are no duplicates in the original listbox.

Rory Over a year ago

It doesn't clear existing entries in column A if that's what you mean. You'd need to add that - this just appends to the bottom.

squar_o Over a year ago

Apologies, I realised it appends to the end and must have been running it twice by accident :/

Collectives™ on Stack Overflow

VBA multiselect listbox, populate column with selected items

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related