userform multiselect listbox problem

hello

can anyone tell me what is going wrong with the following code.

when I have multi items selected when it executes the red line it clears all of the selections in my listbox, however this code works fine on a sample workbook and I cannot see any difference with listbox properties etc - HELP!!!

Dim lItem As Long
For lItem = 0 To Me.lbManifestCurrent.ListCount - 1
If Me.lbManifestCurrent.Selected(lItem) = True Then
Worksheets("Freight Accrual History").Range("C65536").End(xlUp)(2, 1) = Me.lbManifestCurrent.List(lItem)
Me.lbManifestCurrent.Selected(lItem) = False
End If
Next

hello again

i have fixed the intitial problem in that the dynamic range that i named contained 2 columns of data (manifest # in 1st column & $$ charged in 2nd column) and this was then populated in the listbox using rowsource "ManifestsCurrent" using 2 in column count.
While I have fixed the problem i would still prefer to use my initial format with the 2 columns. Can anyone suggest code for this to add both columns of data selected in the listbox to a specified worksheet in columns A & B for example.

Thanks in advance
Darren

A bit of background first I have the following table on sheet1 in columns a:e representing current outstanding invoices

Site....DeliveryDate....Manifest#....Accrual$$....Tonnage
TW......17/07/06........1001.............312.74.......14.520
RK.......17/07/06........1002.............926.59.......15.235
YD......18/07/06........1003.............474.02........24.510
TW......24/07/06........1004.............529.91........24.601
YD.......24/07/06........1005.............698.33........36.108
LI.........07/12/06........1006............817.41.........24.658
IP0........1/11/06.........1007............129.81........12.566
SC........31/01/06........1009............240.90........12.456
MK........02/01/06........1010 .........1,207.87........15.650
IP.........02/01/06........1025............127.58........12.350

I am populating my multiselect listbox on my "Process Invoices" UserForm from column "c" (Manifest#) as a dynamic range name in the rowsource property of the listbox. When I select this particular manifest# I would like to copy the entire row of that particular manifest# from the above worksheet into another worksheet as "processed invoices" and then delete that row from the above. hopefully this makes sense.

Regards darren

Perhaps something like this pseudo code:

Private Sub Cmd_ProcessList_Click()
Dim lItem As Long, rng as Range, rng1 as Range
Dim rng2 as Range
set rng = Range("ManifestsCurrent")
For lItem = 0 To Me.lbManifestCurrent.ListCount - 1
If Me.lbManifestCurrent.Selected(lItem) = True Then
if rng1 is nothing then
set rng1 = rng.Rows(i + 1)
else
set rng1 = Union(rng1,rng.Rows(i))
end if
end if
Next
if not rng1 is nothing then
set rng2 = Worksheets("Freight Accrual History") _
.Range("A65536").End(xlUp)(2, 1)
rng1.copy Destination:=rng2
Me.lbManifestCurrent.RowSource = ""
rng2.Delete Shift:=xlShiftUp
Me.lbManifestCurrent.RowSource = "ManifestsCurrent"
End if
End Sub

--
Regards,
Tom Ogilvy


"apndas" <apndas.2beb7f_1153653605.7704@excelforum-nospam.com> wrote in
message news:apndas.2beb7f_1153653605.7704@excelforum-nospam.com...
>
> A bit of background first I have the following table on sheet1 in
> columns a:e representing current outstanding invoices
>
> Site....DeliveryDate....Manifest#....Accrual$$....Tonnage
> TW......17/07/06........1001.............312.74.......14.520
> RK.......17/07/06........1002.............926.59.......15.235
> YD......18/07/06........1003.............474.02........24.510
> TW......24/07/06........1004.............529.91........24.601
> YD.......24/07/06........1005.............698.33........36.108
> LI.........07/12/06........1006............817.41.........24.658
> IP0........1/11/06.........1007............129.81........12.566
> SC........31/01/06........1009............240.90........12.456
> MK........02/01/06........1010 .........1,207.87........15.650
> IP.........02/01/06........1025............127.58........12.350
>
> I am populating my multiselect listbox on my "Process Invoices"
> UserForm from column "c" (Manifest#) as a dynamic range name in the
> rowsource property of the listbox. When I select this particular
> manifest# I would like to copy the entire row of that particular
> manifest# from the above worksheet into another worksheet as "processed
> invoices" and then delete that row from the above. hopefully this makes
> sense.
>
> Regards darren
>
>
> --
> apndas
> ------------------------------------------------------------------------
> apndas's Profile:
> http://www.excelforum.com/member.php...o&userid=36137
> View this thread: http://www.excelforum.com/showthread...hreadid=564054
>

Thanks Tom

in the interim i came up with this code which seems to work although from a technical point of view is probably not the best solution. I will test your code and see how it goes

'copy data to database


For lItem = 0 To lbManifestCurrent.ListCount - 1
If lbManifestCurrent.Selected(lItem) = True Then
With ws1
Dim lRow As Long
lRow = ws1.Range("a65536").End(xlUp).Row
For i = lRow To 11 Step -1
If Cells(i, 3).Value = lbManifestCurrent.List(lItem) Then
Set rng = Worksheets("Freight Accrual History") _
.Cells(Rows.Count, 1).End(xlUp)(2)
Rows(i).EntireRow.Copy rng
Cells(i, 7).Value = "x"
Exit For
End If

Next i
End With

lbManifestCurrent.Selected(lItem) = False
End If
Next

'delete rows transferred from current to history

For i = lRow To 11 Step -1
If Cells(i, 7).Value = "x" Then
Rows(i).EntireRow.Delete
End If
Next i

this way it was not altering my listbox until all of the selected listbox items had been placed into history