data from listbox to cells

[Buddy Lee]

I've a 6-column listbox and I set the RowSource property to a named range. I
tried to copy selected row from listbox to worksheet but it does not work
( sometimes does, sometimes does not I dont understand why ) What is
wrong?

For index = 1 To 6
Worksheets("sht1").Cells(7 + index, 9) =
ListBox1.list(ListBox1.ListIndex, index - 1)
Next index

 

[Tom Ogilvy]

Only problem I would see is if ListIndex = -1 (nothing selected)

 

[Buddy Lee]

but that is impossible, I set ListBox1.ListIndex = 0 when form is activated
is there another way to do this ? ( fill 6-column listbox with data than
copy selected row to cells )
maybe the problem is something else, I dont know

 

[Tom Ogilvy]

I used this:

Private Sub CommandButton2_Click()
Dim index As Long
For index = 1 To 6
Worksheets("sht1").Cells(7 + index, 9) = _
ListBox2.list(ListBox2.ListIndex, index - 1)
Next index

End Sub

Make sure the listbox has Columncount = 6

Make sure you fully qualify your rowsource. Example mine was Sheet2!A2:F29

It worked fine for me (as expected) (my lisbox was names Listbox2 because I
had another listbox on the form - but as long as you have the correct name,
shouldn't be a problem).

I also tried it in the click event of the listbox2 - worked as well

Private Sub ListBox2_Click()
Dim index As Long
If ListBox2.ListIndex <> -1 Then
For index = 1 To 6
Worksheets("sht1").Cells(7 + index, 9) = _
ListBox2.list(ListBox2.ListIndex, index - 1)
Next index
End If

End Sub