Newbie in VBA Drag and Drop..


  • Hi,

    Do not know if this is possible drag and drop effect of items in dropdown control inside a word application?

    The task is to import and export data from and to database, but user can select from a list columns what they want to add, there will be a dialog will appear and allow user to select data to be included in a word document, how can this be done?

    Thanks in advanced.



    Tuesday, December 12, 2006 11:28 AM

All replies

  • Hi,

    After a bit of searching I found this code that demonstrates drag and drop between two list boxes on a user form.  You saw your new so I'm give a brief overview of whats going on here. ListBox1 is populated with some values when it initialises.

    When the user click ListBox1 (the source) and moves the mouse it causes the ListBox1_MouseMove event to fire. In here you store the data thats being dragged, this is stored in a DataObject like this....

    MyDataObject.SetText ListBox1.Value

    You then need to start the drag and drop which you do with MyDataObject.StartDrag. The Effect is essentially what kind of drag and drop your doing, could be a copy drag and drop, or it could be a drag and drop where the data is removed from one list and moved to another, this is the Effect and changing it to one or the other alters the icon.

    When the user drags the DataObject over ListBox2 the BeforeDragOver event is raised. In here you can determine, based on the data, whether or not ListBox2 accepts the dragged data. If it doesn't you can change the Effect to indicate that the user cannot drop. Look up the help on the 'BeforeDragOver' event and you'll get more info.

    Once the user lets go of the mouse while on ListBox2 the BeforeDropOrPaste event occurs and in here you take the data from the DataObject and put it into the list. Again look at the help on this event to understand it more.

    Hopefully this example will give you an idea of whats going on. Drag and Drop is easier once you see it working. Create a user form with two listboxes and copy the code below into it.

    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


    'this starts the drag and drop from ListBox1
    Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        Dim MyDataObject As DataObject 'data object stores the data being dragged
        If Button = 1 Then 'left ouse button
            Set MyDataObject = New DataObject
            Dim Effect As Integer
            MyDataObject.SetText ListBox1.Value 'set the text of the object being dragged
            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


    Tuesday, December 12, 2006 7:13 PM
  • Hi Derek,

       Wait, where would I start, should I use vb6 and what template/project type would I use, or use And how this application be executed when a word document is open? Don't know all this.


    Wednesday, December 13, 2006 2:07 AM
  • Alright mate,

    That's a very difficult question to answer. You've got three options. You can pull the data from the source (word) to the target (database) in which case, if the database is Access, you'd write the code in the Access database (VBA), this approach wouldn't work so well for SQL Server. An other option would be to push the data from the source (word) to the target (database). You could use VBA in Word for that, or a VSTO (.NET for Office) addin, which would be preferred as VBA has had it's day and is no longer supported by Microsoft. This approach would work whatever your database. Your third option is to have a stand alone application that pulls from the source and pushes to the target, it would be best to do this in .NET, however it all depends if the machines running the application have the framework installed. 

    The answer to this all comes down to how the software will be used, who will be using it, what do they have on their machine, what database your using, any future updates to the software, and over things like that.

    I cannot give you a preferred approach because I don't have all that information. It's tricky bud.

    Wednesday, December 13, 2006 5:55 PM
  • Hi Derek,

        database I guess is SQL Server 2005, OS is XP with multi-language support, office 2003 (not sure if sp1 or sp2), ordinary non-programmer will use it, so they will modify the word document and upload it and data modified will be written on the database, first they would export the data in word document or excel or any template (word) and enter data. Is this clear already? Well, may be if possible not to use VSTO it would be nice.





    Thursday, December 14, 2006 3:33 AM
  • Hi,

    You description is clear enough but you know it's still difficult to make a decision for you without having more detailed requirements. I wouldn't like to make a suggestion based on the information you've given me, you could go and develop something that wasn't suitable based on my suggestion and then users will have another reason to moan about software.

    It would be better for you to write a decent post describing what the system needs to do, list all the steps the user will do in order to use the system, then write about the approach your think would be best, why you think that's the best approach, and then we could take it from there.

    The most important thing is list the steps, for example...

    1. User opens an Excel template

    2. User fills in X, Y, and Z

    3. User mail merges Y and Z to a table in Word document and enters P

    4. User uploads Y and P into three different tables in SQL Server database.

    If you do that from when the user starts the process (opens a spreadsheet) to when they finish the process (save to database) then you might see the best approach is, but post it and what you think is best and I'll give you some feedback. Be as descriptive as possible.

    Thursday, December 14, 2006 8:59 PM
  • Hi Derek,

        Ideally, there would be a web application where

    1. you would export the data with word document and vice-versa,

    I am mentioning one of functions, and

    2. when the user opens this word document, there is a dialog window would appear where user can drag and drop an item in the combobox or dropdown control, and data would be written on the word document,

    3. user can change this data, and upload them,

    4. also user can change the arrangement, add, modify and remove data from the word document template and upload changes back to database.

    Ideally, also the less installation of components or add-ins the better, much simpler and less tedious for user. Is this clear enough?

    Thanks for replying.




    Monday, December 18, 2006 7:12 AM
  • Hi,

    Looks like your best to create this in a Word template using VBA.

    You'll need a user form which will get the data from the web, display it in a list, let the user modify and upload, and also letting the user perform the drag and drop, and uploads.

    Since the user is primarily working with word thats where you'd want to do most of the work.

    you could argue that there are a number of ways you could do this but I say use VBA since your not really wanting to perform any installs.

    Monday, December 18, 2006 7:57 PM
  • Hi Derek,

        Sorry for late reply, busy here, interested on how the drag and drop will be done, the drag and drop will be like there is a combobox or dropdown control where the items can be drag and drop on any location on word document. Can you provide some ideas or links or even better some codes how exactly this can be done? Is there xml schema needed, need to validate user will enter to the word document before the document can be uploaded. Is it possible also inside the document teher will be a button to upload the data entered back to database? The database will be SQL Server 2005 Express Edition or otehr version.  Thanks.



    Thursday, December 21, 2006 1:28 AM
  • Hi man.
    Am computer programmer by provation.for me when dealing with drag drop in vba ,i use image combo control which has
    OLEdragdrop event.
    You use the data argument to get draged filename and its path.
    I created a media player with it in vba and worked great.

    Hope it is helpfull.
    Wednesday, July 13, 2011 6:18 PM