userform multiselect listbox problem

[apndas]

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

 

[apndas]

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

 

[apndas]

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

 

[Tom Ogilvy]

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

 

[apndas]

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