Solved: Drag and Drop within VBA

[hairywhiterabbit]

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.

[Scottie P]

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

[Scottie P]

"Pay dirt!"

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

Scott

[Jacob Hilderbrand]

Works for 2002 as well. So your gonna add this to the kb right?

[Scottie P]

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.

[hairywhiterabbit]

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

[Scottie P]

Glad to be of assistance!


Scott

[salman.sh]

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