Moving multiple column rows between 2 listboxes

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

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

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

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

    'if the list is not empty
    If lstMoveFrom.ListIndex <> -1 Then

        itemIndex = lstMoveFrom.ListIndex
        
        With lstMoveFrom
            
            If .Selected(itemIndex) Then
                lstMoveTo.AddItem .List(itemIndex)
                'if this is a multicolumn list then add other column info
                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
                .RemoveItem itemIndex
            End If
        End With

Thanks

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

Hi Andy

I got it working using the array method.

    ' if the list is not empty
    If lstMoveFrom.ListIndex <> -1 Then

        itemIndex = lstMoveFrom.ListIndex
        insertedItem = 0

        With lstMoveFrom

            If .Selected(itemIndex) Then
            
                'if this is a multicolumn list then add other column info
                If numCols > 1 Then

                    If lstMoveTo.ListCount > 0 Then insertedItem = lstMoveTo.ListCount
                    ReDim dummyArr(insertedItem, numCols - 1)
            
                    ' Get all the rows and columns from the MoveTo list
                    For i = 0 To insertedItem - 1
                        For j = 0 To numCols - 1
                            dummyArr(i, j) = lstMoveTo.List(i, j)
                        Next
                    Next
                    
                    ' When we add this we only need to add the columns
                    lstMoveTo.AddItem .List(itemIndex)
                                        
                    ' Now add the new columns from the old list
                    For i = 0 To numCols - 1
                        dummyArr(insertedItem, i) = lstMoveFrom.List(itemIndex, i)
                    Next i

                    ' Now add the complete array (old and new records) back to the lstMoveTo
                    lstMoveTo.List = dummyArr

                End If
                .RemoveItem itemIndex
            End If
        End With

    End If

Thanks for the link though.