Moving multiple column rows between 2 listboxes

[DazlerD]

Hi

I have a userform with 2 listboxss. One listbox contains Currency info with 13 columns in it. The first column specifies the currency (GBP, Euro etc) and the other 12 are decimal values for each of the 12 months.

When the user double clicks a row I need to copy the complete row to the other listbox. It can go from right to left and left to right.

Im using this code:

If numCols > 1 Then
For i = 1 To numCols - 1
insertedItem = lstMoveTo.ListCount - 1
lstMoveTo.List(insertedItem, i) = lstMoveFrom.List(itemIndex, i)
Next i
End If

However only the first 10 columns are copied due to constraints on listboxes with unbound data sources.

Any body have any ingenious ways to get round this?

Thanks

[Norie]

First can I ask why you want to move the12 columns?

What's the next step after you've done that?

Does it involve choosing a month?

[DazlerD]

Hi Norie

Columns in the left currency listbox are values saved into a sheet in the current xls.

The listbox on the right are currency values coming in from a loaded xls.

So i can remove currency entries from the current xls, or import as i wish from the import xls

Cheers

[Norie]

Sorry you've kind of lost me there.

You have 2 listboxes each populated from a different workbook and you want to move data to and fro between them.

Is that all you want to do?

[DazlerD]

Yeah thats it, quite simple !!

apart from the fact you cant move more than 10 columns using ".item = " from one listbox to another !

Thanks for your help

Darren

[Norie]

Darren

I know that's a problem and I'm kind of trying to think if there's a way round it by taking a slightly different approach.

You can actually do it with arrays (or ranges) and List but that's a bit fiddly.

Any chance you could upload a sample workbook?

[DazlerD]

Hi

Cant send a worksheet as the code is created on the fly in a userform.

But, heres the code to move the record between listboxes

Please Login or Register  to view this content.

Thanks

[Norie]

Why does that stop you uploading a workbook?

[DazlerD]

Cause the real reason is that the xls has loads of confidential data and so does the workbook to import.

I'm working on storing the MoveTo records and fields into an array, then adding the MoveFrom record and using the MoveTo.list to add them all back.

If I cant get it working ill create a test xls in the morning and submit that.

Thanks

[Andy Pope]

if you populate with array you can exceed 10 columns and use the List array property.

userform with 2 listboxes and a button to transfer selected list row from 1 to the other.

Please Login or Register  to view this content.

[DazlerD]

Hi Andy

I got it working using the array method.

Please Login or Register  to view this content.

Thanks for the link though.