ListBox items paste into worksheet in reverse order
 

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

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=53641

ListBox items paste into worksheet in reverse order
 

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

--
Regards,
Tom Ogilvy


"Casey" wrote:


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

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

Here is the code which inserts the Listbox selections into the
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.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=536412

ListBox items paste into worksheet in reverse order
 

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

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=53641

ListBox items paste into worksheet in reverse order
 

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.

--
Regards,
Tom Ogilvy


"Casey" wrote:


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


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=536412

ListBox items paste into worksheet in reverse order
 

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


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=536412