CODEWITHSUNDEEP
vbaexcellistboxexport
Singularity20XX
Singularity20XX

Reputation: 321

Exporting items in list box to range in Excel

So I have scoured google and forum after forum (including the Stack) trying to figure this out. All I want to do is have an ActiveX button export the contents of a list box to a range in Excel.

Below is the code I have that adds items from ListBox1 to ListBox2. After all desired items are moved to ListBox2, said ActiveX button (SomeButton_Click) would then export all the items in ListBox2 to "Sheet15" starting at range("a1").

(Please note, this is an ActiveX ListBox that is not on an actual form. It is within a worksheet)

Public Sub BTN_MoveSelectedRight_Click()
    Dim iCtr As Long
        Dim n As Long, lRow As Long
         Dim cStartCell As Range
    For iCtr = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(iCtr) = True Then
        Me.ListBox2.AddItem Me.ListBox1.List(iCtr)

        End If
    Next iCtr

    For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox1.RemoveItem iCtr
        End If
    Next iCtr

End Sub

Below would be the button that would perform the export:

Public Sub SomeButton_Click()
'What code can I put here to perform the export to abovementioned range?
End sub

Any help would be extremely appreciated as I have spent hours trying to figure this out (as much as I don't want to actually admit this, it is true)

Thank you!

enter image description here

Upvotes: 1

Views: 3711

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

Here you go:

Public Sub SomeButton_Click()
    Dim v
    v = Sheet15.ListBox2.List
    Sheet15.[a1].Resize(UBound(v)) = v
End Sub

If the listbox is on a different worksheet, you'll need to adjust that.

Edit #1

This is better:

Public Sub SomeButton_Click()
    With Sheet15
        .[a1].Resize(.ListBox1.ListCount) = .ListBox1.List
    End With
End Sub

Upvotes: 3

Related Questions