Double-clicking to move an item between list boxes

[patjones]

Sometimes you might have two list boxes side by side, and want to double-click on an item in one box to move it to the other.

For instance, maybe the first list box contains a list of customers, and you want to perform some function (say, email them) that pertains to only a few of those customers. By double-clicking on the customer names you want, you get the desired list in the right hand box.

First, make a generic sub that takes two listbox arguments:

 
Private Sub MoveListItem(lstFrom As ListBox, lstTo As ListBox)
     lstTo.AddItem (lstFrom.Value)
     lstFrom.RemoveItem (lstFrom.Value)
End Sub
 

Then call this sub in the double-click events for the respective list boxes:

 
Private Sub lstA_DblClick(Cancel As Integer)
    MoveListItem Me.lstA, Me.lstB
End Sub 
 
Private Sub lstB_DblClick(Cancel As Integer)
    MoveListItem Me.lstB, Me.lstA
End Sub 
 

Here you'll replace "lstA" and "lstB" with whatever the names of your list boxes are. You could modify this slightly by having a button situated between the boxes, and move the item from one to the other when the button is clicked.

It's a simple task but can come in handy!

Pat

[MMcCarthy]

Pat

Does this belong in VB. I don't think you can use .AddItem and .RemoveItem in VBA without at least adding a library.

If it needs to be moved just post a comment here and I'll take care of it.

Mary

[patjones]

Pat

Does this belong in VB. I don't think you can use .AddItem and .RemoveItem in VBA without at least adding a library.

If it needs to be moved just post a comment here and I'll take care of it.

Mary

I didn't have any reference issues when using this code in VB 6 (Access). Do you want me to post which references I have enabled just to be sure?

Thanks!

Pat

[JustJim]

@zepphead80
Hi Pat,

This is simple and elegant but, sadly errors out for me. Perhaps a library problem as Mary thought. I tried it at home using Access '03/VBA Retail 6.4.8869 with the following references included:

Visual Basic for Applications
Microsoft Access 11.0
Microsoft DAO 3.6
Microsoft ActiveX Data Objects 2.1
OLE Automation

Anything else you have loaded that might be relevant?

The error is 94: Invalid use of Null, and is on .AddItem line of the MoveListItem sub. A little hovering tells me that ListFrom.Value is null as is Me.lstA and Me.lstB from the calling subs.

Also, the helpfile for .AddItem and .RemoveItem states that the list box RowSourceType must be "Value List", which means you would have to build the contents of the first listbox by code or hard key it when you design the form. A pain, but a mild one.

Jim

[JustJim]

Sorry, ignore almost all of the above - I had one of the list boxes set to Extended Selection and that messed it up for some reason.

It now works fine with the basic libraries above, thank you.

The comment about the RowSourceType should be noted by others though.

(Blushing)Jim

[patjones]

Glad that my post was of assistance to you! No need to blush - show me someone who doesn't make these kinds of mistakes when programming in Access!

Pat

[MMcCarthy]

Hi Pat

Haven't seen this thread in a while. My misinformation on .AddItem is probably a holdover from some of those earlier versions of Access libraries. I haven't tried to use it in years. I'll have to start experimenting again.

Thanks for the information. I always learn something new from the bytes experts no matter how much I think I know. It's why I love information sharing sites like this. Stops me from getting stuck in old patterns.

Mary