Drag and Drop (Excel 2K SR1)

Topic

New Reply

I would like to be able to drag items from one list box to another. Can anyone provide detailed instructions or a working example for me? Any help will be appreciated.

Thanks,

Reply | Quote

Replies

I’m not sure if this is possible. I’ve coded dual-list boxes with those ‘add/remove’ arrow things (>> <<) to have users move items from one list to another but that's not true drag/drop (and seeing a shadow as the object moves).

I looked up drag and drop in an Excel VBA book I have and they only refer to the events BeforeDragOver and BeforeDropOrPaste for dataobject controls.It implies that you can only respond to the action after it happens (like from Windows Explorer to an Excel object) and then you can grab/intercept the dragged item from the clipboard and do what you want with it at that point.

This isn't what you asked for though.

The listbox control has MouseDown/KeyDown events so you would know at this point what item they selected. I’m not sure how to differentiate between a simple mouse down (they selected the item) and the desire to drag which is usually a prolonged mouse down event (they hold they left key longer, as long as it takes to move the item to the other list). But then again you can’t assume that any mouse down event is intended for a drag/drop. What if they ‘dropped’ the selected item off of the list, somewhere else, then that should be an ignored or aborted drop/paste operation. If you could get the xy coordinates of the borders of the ListBox and compare it to the position of where the MouseUp event occured then you could tell if the drop appeared over the list box. I’m rambling at this point, thinking out loud what would be needed to support a true drag/drop operation.

Of course you can drag/drop directly on a worksheet between cells. The user can enable/disable this feature from the Edit tab on the Options menu. In Excel 2000 you also have a multi-item clipboard (up to 12 items can be stored) so you could store the selected item there and when you get the mouseup event check if the mouse is over the list box and then paste the stored clipboard item (but first check to make sure the user has deleted the item already or the clipboard has rolled over (gone past 12 item limit and popped off the oldest item).

Ok, I’m done rambling…. I hope some of this gives you ideas to try.

Deb

Reply | Quote

As Deb said, it doesn’t appear that the MS Forms listbox control supports drag and drop.
Attached is an Excel file illustrating a possible workaround, that takes advantage of the textbox control’s support for drag and drop. It may be too funky to actually use but it’s interesting anyway…
(This was done using Excel 2002 but I think it should work the same in Excel 2000.)

By the way the VB6 listbox control (i.e. in Visual Studio) supports use as a drag source, but it doesn’t appear to allow you to drop anything into it.

Gary

Reply | Quote

For further evidence that Windows listboxes don’t support drag and drop, have a look at the Organizer dialog in Word – presumably this was written in some variety of C, but in any case you need to click on the Copy button that’s between the two listboxes, to move items from one listbox to the other.

Attached is an illustration from a custom userform that does something similar (the listboxes don’t have any contents ’cause they are pictured in design mode rather than runtime but you can get the idea).

Reply | Quote

It is possible to simulate drag and drop between list boxes; I have attached a rather clunky example (based on sample code for Access in Microsoft Knowledge Base Article 233274). Click the command button om the worksheet to view the user form. You can drag items from one list box to the other, but there is no visual feedback while dragging. Operation is not quite dependable, so this is meant as a demo only!

The only example using BeforeDragOrDrop I found is on http://www.experts-exchange.com/Applicatio…Q_20160268.html%5B/url%5D – scroll to the bottom of the page. It’s not about list boxes, though.

Reply | Quote

The attached seems to work in Excel 2002 – can’t test it in 2000 at the moment. It’s very simplistic but may work for your needs (if it does work!)
HTH.

Reply | Quote

Thank you for your examples. The form created in Excel 2002 would not import in to Excel 2000, so I am not sure if it was what I am looking for. The other example worked, but as indicated, it provided no visual indicator. I know that Drag an drop can be added because I have a sheet visibility utility that I downloaded form xl-Logic.com, which uses drag and drop. It is a regular .xla Add-in and not a COM add-in.

The utility can be downloaded from: “http://www.xl-logic.com/pages/addins.html&#8221; if you wish to review it.

Thanks for all of your help.

Reply | Quote

Mark,
It obviously got corrupted somewhere down the line as I can’t import it back into Excel 2002 either! The form has 2 listboxes and the following code:

Private Sub ListBox2_BeforeDragOver(ByVal Cancel As _
    MSForms.ReturnBoolean, ByVal Data As _
    MSForms.DataObject, ByVal X As Single, _
    ByVal Y As Single, ByVal DragState As Long, _
    ByVal Effect As MSForms.ReturnEffect, _
    ByVal Shift As Integer)
    Cancel = True
    Effect = 1
End Sub

Private Sub ListBox2_BeforeDropOrPaste(ByVal _
    Cancel As MSForms.ReturnBoolean, _
    ByVal Action As Long, ByVal Data As _
    MSForms.DataObject, ByVal X As Single, _
    ByVal Y As Single, ByVal Effect As _
    MSForms.ReturnEffect, ByVal Shift As Integer)
    Cancel = True
    Effect = 1
    ListBox2.AddItem Data.GetText
End Sub

Private Sub ListBox1_MouseMove(ByVal Button As _
     Integer, ByVal Shift As Integer, ByVal X As _
     Single, ByVal Y As Single)
    Dim MyDataObject As DataObject
    If Button = 1 Then
        Set MyDataObject = New DataObject
        Dim Effect As Integer
        MyDataObject.SetText ListBox1.Value
        Effect = MyDataObject.StartDrag
    End If
End Sub

Private Sub UserForm_Initialize()
    For i = 1 To 10
        ListBox1.AddItem "Choice " _
            & (ListBox1.ListCount + 1)
    Next i
End Sub

FWIW.

Reply | Quote