Move data from 2 column listbox to another 2 column listbox on double click

[ceddins]

Good morning,

This is my first time to work with userforms and listboxes, and I've been trying for many, many hours to get this to work. I have a userform with 2 listboxes. All I want to do is transfer a single 2-column row of data from listbox1 (a 2-column listbox) when a user double clicks a value over to listbox2 (another 2-column listbox). If the user double clicks 3 different values in listbox1, then each of those 2-column rows would be added as the next available row in listbox2.

Then, when the user has filled listbox2 with the values he/she wants, the user clicks a button that takes each 2-column row in listbox2, concatenates the values with a space between, and pastes them into a range.

Can someone help me, please?

 

[Andrzejek]

Could you show your code of how you populate listbox1?
And the code of what have you tried to move the data to listbox2?

Have fun.

---- Andy

 

[ceddins]

Hi Andy,

The first lisbox (lbChoose) is populated by using an advanced filter and a dynamic named range on the results.
Here is the code for that:

Private Sub btFind_Click()
Dim j()
Dim x As Long
Dim y As Long
ThisWorkbook.Sheets("Filter").Range("A2").Value = tbSearchValue.Value
On Error GoTo ErrorHandler
Sheets("DATA").Range("AR1:CC1").EntireColumn.Clear
Sheets("DATA").Range("All_Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets _
("Filter").Range("A1:A2"), CopyToRange:=Sheets("DATA").Range("AR1"), Unique:=False
ReDim j(1 To ThisWorkbook.Sheets("Data").Range("YearMth").Count, 1 To 2)
For x = 1 To ThisWorkbook.Sheets("DATA").Range("YearMth").Count
j(x, 1) = Format(ThisWorkbook.Sheets("DATA").Range("YearMth")(x, 1), "YYYY-MM")
Next
For y = 1 To ThisWorkbook.Sheets("DATA").Range("Booking_Post_As").Count
j(y, 2) = ThisWorkbook.Sheets("DATA").Range("Booking_Post_As")(y, 1)
Next
lbChoose.List = j
Exit Sub
ErrorHandler:
MsgBox "Did not find a match. Please try again"
Exit Sub
End Sub

I've gone through a ton of differet methods to try and populate listbox2 (lbChosen). None of them has worked. Here's the latest failed attempt:

Private Sub lbChoose_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If lbChosen.ListIndex = -1 Then
Exit Sub
End If
Dim Listbox2(1, 1 To 2)
Dim x As Long
Dim y As Long
For y = 1 To 2
For x = 1 To 1
Listbox2(x, y) = lbChoose.List(lbChoose.ListIndex, y - 1)
Next
Next
lbChosen.List(lbChosen.ListCount) = Listbox2
btShowResults.Enabled = True
End Sub

 

[Andrzejek]

I created a simple Form with 2 listboxes (lbChoose and ListBox2), each with 2 columns
To simplify, I hard-coded the values going into lbChoose, but you have it already.

Here is code:

Option Explicit

Private Sub UserForm_Activate()
Dim i As Integer

With lbChoose
    For i = 0 To 5
        .AddItem
        .List(i, 0) = "Col " & i
        .List(i, 1) = "Something" & i
    Next i
End With

End Sub
[blue]
Private Sub lbChoose_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Static X As Integer

Me.Caption = lbChoose.ListIndex

With ListBox2
    .AddItem
    .List(X, 0) = lbChoose.List(lbChoose.ListIndex, 0)
    .List(X, 1) = lbChoose.List(lbChoose.ListIndex, 1)
End With

X = X + 1

End Sub[/blue]

I guess you just need the code in blue

Have fun.

---- Andy

 

[Andrzejek]

Ooops, you don't need this line of code:
[tt]Me.Caption = lbChoose.ListIndex[/tt]
That was just testing on my part....

Have fun.

---- Andy

 

[ceddins]

Andy, that did the trick! Thank you so much. Your code worked after I removed my error handling line:

If lbChosen.ListIndex = -1 Then
Exit Sub
End If

I was referring to the wrong listbox. It should have read 'If lbChoose.ListIndex = -1'

Have a great weekend and thanks again.

-Clint

 

[Andrzejek]

I am glad you have solved your problem.

But you lost me here: "Your code worked after I removed my error handling line"
So do you have now your code with 2 For...Next loops, or my 5 lines of code in lbChoose_DblClick event?

Have fun.

---- Andy

 

[ceddins]

This is the code I ended up using. It's your code, but with a couple of things added around it.

Private Sub lbChoose_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If lbChoose.ListIndex = -1 Then
Exit Sub
End If
Static X As Integer
On Error GoTo ErrorHandler
With lbChosen
.AddItem
.List(X, 0) = lbChoose.List(lbChoose.ListIndex, 0)
.List(X, 1) = lbChoose.List(lbChoose.ListIndex, 1)
End With

X = X + 1
btShowResults.Enabled = True
Exit Sub

ErrorHandler:
Exit Sub
End Sub

 

[Andrzejek]

I don't think you need this:

[pre]
If lbChoose.ListIndex = -1 Then
Exit Sub
End If
[/pre]
ListIndex = 0 is the very first 'entry' (item) in your list box, ListIndex = - 1 is the 'previous' one, which you cannot click on becasue..., well, it is not there.

I do use ListIndex = - 1, but only with combo boxes when I want to have an 'empty' combo - nothing selected.



Have fun.

---- Andy