Pegasus‒A VBA based Drag and Drop tool in MS Excel [2/2]

Hello friends!

I apologize for the delay in this post. Firstly, here is the working version of the tool (click here to download).

I’m going to keep this post short and simply going to upload the working version of Pegasus for you to explore. The principle by which the drag and drop feature works in Pegasus is really simple—it activates the built in properties of two list boxes wherein a value from one list box is dragged into another. The trick here is to make the second list box (to which the value is being dragged) transparent so that the user is able to see the formatting on the sheet.

Although the underlying code for the tool is self-explanatory and most of it should be easy to follow, I would like to highlight the following important snippets:

1. Code to retrieve the text value  being dragged from the list box

Private Sub ListBox2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)

Dim Data_obj As DataObject

Dim Drag_val As Long

Const Leftmouseclick As Long = 1

If Button = Leftmouseclick Then

        Set Data_obj = New DataObject

        For i = 0 To ListBox2.ListCount – 1

        If ListBox2.Selected(i) Then

        Exit For

        End If

        Next i

        On Error Resume Next

        ProcessingSheet.Range(“M2″) = ListBox2.List(i)

        Drop_parameteR = 2

        Drag_val = Data_obj.StartDrag

        Duplicate_check = False

End If

End Sub

2. Code to input the text value  being dragged to a new list box

Private Sub ListBox3_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal x As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

 Application.ScreenUpdating = False

 If Drop_parameteR = 2 Then

        DisplaySheet.Range(“E6″) = ProcessingSheet.Range(“M2″)

        DisplaySheet.ListBox3.BackColor = RGB(219, 238, 243)

End If

Application.ScreenUpdating = True

End Sub

 

All the Excel and VBA features that I highlighted in my previous post have been used in this tool and can be explored—the tool and its components are open for everyone.

Also, in case you have any specific queries, please feel free to contact us at query@crabsheet.com and we would be happy to help you. Till I come back with a new tool in my next post, happy Excelling! :)

Have a question? just leave a comment and we will answer A.S.A.P