Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drag and Drop (Excel 2K SR1)

    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,

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drag and Drop (Excel 2K SR1)

    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. <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    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.... <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> I hope some of this gives you ideas to try.

    Deb

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Drag and Drop (Excel 2K SR1)

    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

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Drag and Drop (Excel 2K SR1)

    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).

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drag and Drop (Excel 2K SR1)

    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 <!mskb=233274>Microsoft Knowledge Base Article 233274<!/mskb>). 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 - scroll to the bottom of the page. It's not about list boxes, though.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drag and Drop (Excel 2K SR1)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drag and Drop (Excel 2K SR1)

    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" if you wish to review it.

    Thanks for all of your help.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drag and Drop (Excel 2K SR1)

    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:
    <pre>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
    </pre>

    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •