copy values from listbox to worksheet

[ahmad123]

Helloooo,

I have a multi selction listbox, i need to copy all the values that i selected in this list box into a column in a worksheet :

1- Select all the values from the list
2- Click on a button
3- Unload the listbox and get the selectd values in in column "A"

Is there a way to do so ??

Thanx for any input

 

[VoG]

Try like this

For i = 1 To Me.ListBox1.ListCount
  Cells(i + 1, 1) = Me.ListBox1.List(i - 1)
Next i

 

[ahmad123]

Hi VoG,

this is copying all the values in the list and pasting it in the right column. I need to copy the selected values only (the highlighted ones)

Thx in advance

 

[VoG]

Maybe

For i = 1 To Me.ListBox1.ListCount
  If Me.ListBox1.Selection(i).Selected = True Then
  j = j + 1
  Cells(j, 1) = Me.ListBox1.List(i - 1)
Next i

 

[ahmad123]

I am getting "argument not optionnal" compile error, for ".selected".

Any ideas on how to fix that ??

 

[ZVI]

Try this:

' Code of UserForm1 module
Const SourseAddr$ = "F1:I10"
Const DestAddr$ = "A1"

Private Sub UserForm_Initialize()
  Dim a
  ' Populate ListBox1
  a = Range(SourseAddr).Value
  With ListBox1
    .ColumnCount = UBound(a, 2)
    .List = a
  End With
End Sub

Private Sub CommandButton1_Click()
  Dim a, i&, r&, c&
  With ListBox1
    a = .List
    For r = 0 To UBound(a)
      If .Selected(r) Then
        For c = 0 To UBound(a, 2)
          a(i, c) = .List(r, c)
        Next
        i = i + 1
      End If
    Next
  End With
  If i Then
    ' Copy selected rows to destination
    Range(DestAddr).Resize(i, UBound(a, 2) + 1) = a
    MsgBox "Copied  items: " & i, vbInformation
    Unload Me
  Else
    MsgBox "Items are not selected", vbExclamation 
  End If
End Sub