Moving Items Between ListBoxes

[Boechat]

Good afternoon,

I am working on a form containing three listboxes. I have created buttons to be able to freely transfer data from one listbox to another, but I'm stuck at the code. The data that populates the listboxes upon form activation comes from different work sheets in the same workbook. So,when I transfer an item from one listbox to another, the code is supposed to delete the entire row from one sheet and paste it to the destination sheet. After that the form reloads and the listbox is populated with the realoted results.

I have seen some guides on how to create "move to the right" or "move to the left" buttons, but they don't seem to realy apply since they don't effectively transfer the original data between the sheets.

Any ideas?

Thank you all

 

[Boechat]

So I've gotten further, but the code is still not working as it should. It copies and pastes the correct value sometimes, but after the first "copy+paste" it starts getting the wrong row. Also it does not refresh the listbox, it requires me to close the form and open again to show the updates.

Code below:

Private Sub CommandButton2_Click()
Dim NextRow As Range
Set NextRow = Range("A" & Sheets("Amostras-AR").UsedRange.Rows.Count + 1)

Sheets("Amostras-RMC").Range("A2:E500").Cells(Me.ListBox1.ListIndex + 1, 1).EntireRow.Copy
Sheets("Amostras-AR").Activate
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set NextRow = Nothing

Sheets("Amostras-RMC").Activate
Sheets("Amostras-RMC").Range("A2:E500").Cells(Me.ListBox1.ListIndex + 1, 1).EntireRow.Delete

End Sub

 

[Boechat]

Crossposted at Moving items between ListBoxes

 

[mikerickson]

This code is for a user form with two list boxes and two buttons, butOneToTwo and butTwoToOne.
The functions SourceOfOne and SourceOfTwo are assuming that there are headers on the worksheets

Private Sub butOneToTwo_Click()
    If ListBox1.ListIndex <> -1 Then
        With SourceOfOne.Cells(ListBox1.ListIndex + 1, 1).EntireRow
            .Copy Destination:=SourceOfTwo.EntireColumn.EntireColumn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            .Delete
        End With
        FillListBox1
        FillListBox2
    End If
End Sub

Private Sub butTwoToOne_Click()
    If ListBox2.ListIndex <> -1 Then
        With SourceOfTwo.Cells(ListBox2.ListIndex + 1, 1).EntireRow
            .Copy Destination:=SourceOfOne.EntireColumn.EntireColumn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            .Delete
        End With
        FillListBox1
        FillListBox2
    End If
End Sub

Private Sub UserForm_Initialize()
    FillListBox1
    FillListBox2
End Sub

Sub FillListBox1()
    ListBox1.List = SourceOfOne.Value
End Sub

Sub FillListBox2()
    ListBox2.List = SourceOfTwo.Value
End Sub

Function SourceOfOne() As Range
    With ThisWorkbook.Sheets("Sheet1").Range("A2")
        Set SourceOfOne = Range(.Cells(1, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
    End With
End Function

Function SourceOfTwo() As Range
    With ThisWorkbook.Sheets("Sheet2").Range("A2")
        Set SourceOfTwo = Range(.Cells(1, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
    End With
End Function

 

[Boechat]

Thank you so much Mik. This seems to get closer to what I need.

However, I keep getting the error message that the list property cannot be defined. The properties matrix index is invalid.

Any ideas?

 

[mikerickson]

What kind of list boxes are you using. e.g. the use of the .ListIndex property in the OP code suggests that it is a single select listbox. How many columns do they have?

Where are you getting that error message? What line is highlighted?

 

[Boechat]

They are all 3 single select listboxes. Each with 3 columns.

The error message shows up everytime I run debug an get to the line in red:

Function SourceOfOne() As Range
    With ThisWorkbook.Sheets("Amostras-RMC").Range("A2")
        Set SourceOfOne = Range(.Cells(1, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
    End With
[COLOR=#ff0000]End Function[/COLOR]

The message is the one I mentioned above: "List property cannot be defined. The properties matrix index is invalid." (Hope it is correct, I had to somehow translate it in a manner it made some sense)

 

[mikerickson]

That is very weird. I don't understand why that line should be throwing an error.
Any merged cells on that sheet? (He asks, guessing wildly)

 

[Boechat]

No merged cells... Just header over 5 columns. the first 3 are displayed in the listboxes. HOwever, you mentioned that the functions were there in case I had headers, however, the headers are also shown in the listboxes. Is that supposed to eb so?

 

[mikerickson]

I missed the three columns thing.
Change the SourceOfOne code to

Function SourceOfOne() As Range
    With ThisWorkbook.Sheets("Sheet1").Range("A2")
        Set SourceOfOne = Range(.Cells(1, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp)).Resize(,ListBox1.ColumnCount)
    End With
End Function

Similarly for SourceOfTwo