asked on

Add item to list box from another list box

Hello,

I was able to populate a listbox that I created (lets say Listbox A) with Column A from another worksheet (let say Worksheet A).

Now I have another listbox (Listbox B). I want it so that if I double click on the item in Listbox A, it will move that item from Listbox A to Listbox B. However, I also need it vice versa (when I double-click that same item on Listbox B, it will move it back to Listbox A)

Any Ideas?

Thanks
D

lebeau26

ASKER

Also, I want it limit it so that Listbox B can only contain 8 items.

Thanks
D

ASKER CERTIFIED SOLUTION

Jeff Darling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lebeau26

ASKER

The line "Me.ListBoxA.RemoveItem (i) " seems to be giving me a problem.

I get "Unspecified Error"

This is my code :
Private Sub lstCompList_Click()
    Dim i As Integer
 
    For i = 0 To Worksheets("MainPage").lstCompList.ListCount - 1
        If Worksheets("MainPage").lstCompList.Selected(i) Then
            If Worksheets("MainPage").lstChart.ListCount < 8 Then
                Worksheets("MainPage").lstChart.AddItem Worksheets("MainPage").lstCompList.List(i)
                'Worksheets("MainPage").lstCompList.RemoveItem (i)
            End If
        End If
    Next i
End Sub

Open in new window

Jeff Darling

That is because you are using the Click event instead of the DblClick event. Move that code from lstCompList_Click() to lstCompList_DblClick () and it should work.

lebeau26

ASKER

This might sound dumb, but I don't see this option. From my experience in Access, i know that this exists, but I dont see this in Excel.

I right click the list box and the only option it has is 'View Code" which defaults to Click event. I try right-click and then "Properties" and I still dont see this

Any ideas?

Jeff Darling

When you are in the code, there should be two drop down lists above the code. One says lstCompList and the other one says Click. If you select the drop down, you will find DblClick.

You could just manually change the code to rename the function to lstCompList_DblClick () and it should work.

lebeau26

ASKER

Man, I feel stupid.

One last thing...after I add it to the second listbox, how can I sort the items in it?

Thanks
D

Jeff Darling

Unfortunately, listbox control in Excel doesn't have a sorted option. The sort would need to be performed manually. I see if I can find some sort code for a Excel listbox.

lebeau26

ASKER

Thanks, let me know. I can add more points

Jeff Darling

In each DblClick event of each listbox add Call SortListBoxes() to re-sort every time an item is moved.

Public Sub SortListBoxes()
 
    Dim i As Long
    Dim j As Long
    Dim Temp As Variant
    
    With Me.ListBoxA
        For i = 0 To .ListCount - 2
            For j = i + 1 To .ListCount - 1
                If .List(i) > .List(j) Then
                    Temp = .List(j)
                    .List(j) = .List(i)
                    .List(i) = Temp
                End If
            Next j
        Next i
    End With
 
    With Me.ListBoxB
        For i = 0 To .ListCount - 2
            For j = i + 1 To .ListCount - 1
                If .List(i) > .List(j) Then
                    Temp = .List(j)
                    .List(j) = .List(i)
                    .List(i) = Temp
                End If
            Next j
        Next i
    End With
 
 
End Sub

Open in new window