ListBox items paste into worksheet in reverse order

[Casey]

Hi,
I have a UserForm with a listbox, the RowSource is a column range
Everything works fine except when the selected items are inserted int
the spreadsheet, they are entered in reverse order from the colum
range (they appear in the correct order in the listbox). Need som
help.

Example:
Listbox selections Get inserted
Item1 Item7
Item3 Item3
Item7 Item1

Here is the code which inserts the Listbox selections into th
worksheet.

Private Sub cmdEnterSelection_Click()
Dim i As Long
Dim j As Long
j = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
ActiveCell.EntireRow.Insert
ActiveCell.Value = Me.ListBox1.List(i)
j = j + 1
End If
Next i
ActiveCell.Offset(j, 0).Select
OptionButton2.Value = True
End Sub

NOTE: The two optionbuttons are used to "Select All" and "Deselect All
I don't think they are the problem

 

[Tom Ogilvy]

Try this modification:

Private Sub cmdEnterSelection_Click()
Dim i As Long
Dim j As Long
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
ActiveCell.EntireRow.Insert
ActiveCell.Value = Me.ListBox1.List(i)
ActiveCell.Offset(1,0).Select
End If
Next i
ActiveCell.Offset(j, 0).Select
OptionButton2.Value = True
End Sub

 

[Casey]

Tom,
Works perfectly. Thank you very much. Now I just need to study you
code compared to mine to find out why. Thanks again

 

[Tom Ogilvy]

when you insert a row, it pushes the current row down and the activecell is
on the newly inserted row. So you keep adding your list above the item just
added. I only added a single line after the update

ActiveCell.offset(1,0).Activate

this moves the activecell down one row (back to the original row which is
now 1 row down) for the next insert.

 

[Casey]

Tom,
The code help was great; the in depth explaination is above and beyond
and made it crystal clear. Thanks.