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.
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.
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 *~
"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 *~
Works for 2002 as well. So your gonna add this to the kb right?
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 *~
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
Glad to be of assistance!
Scott
Life is Visual: Presence is Perception...
How we see the world is how we respond to it. ~* Peace *~
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