Consulting

Results 1 to 8 of 8

Thread: Solved: Drag and Drop within VBA

  1. #1

    Solved: Drag and Drop within VBA

    Hi,

    I was wondering if it was possible to drag and drop within VBA. I know you can in VB6, but cant seem to find out how to do it in VBA.

    I want to be able to drag list box items from one list box to another.

    Cheers.

  2. #2
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location

    Drag and Drop...

    Hi hairywhiterabbit! Welcome to VBAX!!

    I did a little research and hope this is some help.

    I found only one reference to Drag and Drop for VBA.
    PLEASE NOTE: This example comes from an old Newsgroup article dealing with Excel '97 and is untested but it may give you a direction inwhich to go.

    The discussion starts here:
    _____
    The following example demonstrates a drag-and-drop operation from one
    ListBox to another using a DataObject to contain the dragged text. This code
    sample uses the SetText and StartDrag methods in the MouseMove event to
    implement the drag-and-drop operation.
    To use this example, copy this sample code to the Declarations portion of a
    form. Make sure that the form contains two ListBox controls named ListBox1
    and ListBox2. You also need to add choices to the second ListBox.

    [VBA]

    'In the Code Module of a Userform:

    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()
    Dim i As Long
    For i = 1 To 10
    ListBox1.AddItem "Choice " & (ListBox1.ListCount + 1)
    Next i
    End Sub

    [/VBA]

    _____
    End the discussion

    I will fiddle with this a bit and see what results I come up with. Please post back here with questions/problems and lets see if we can get this project working for you.

    Regards,

    Scott
    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

  3. #3
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location

    Follow up to my previous post:

    "Pay dirt!"

    Yes this code works. I tested it in Excel 2000 and zipped up a sample for you.

    Scott
    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Works for 2002 as well. So your gonna add this to the kb right?

  5. #5
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location
    Hold your breath!

    ADDED:

    Sorry, hit enter when going for the shift key.
    I made a few changes and 'POOF!' I was surprised and happy.
    Thanks for running that in XP.

    I will write it up.
    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

  6. #6
    Sensational guys! Thats perfect!

    Thanks for the welcome and help. These forums look great and hopefully I'll be able to help others in the future.

    Cheers

  7. #7
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location
    Glad to be of assistance!


    Scott
    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

  8. #8

    Cant get it to work

    Dear all,

    I cant seem to find and add the events:

    [VBA]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
    [/VBA]
    AND

    [VBA]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
    [/VBA]
    please guide me through

    Regards
    Salman

Posting Permissions

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