24 thoughts on “Move a userform by dragging within the form”

1. I’m new to VBA so thanks! Reading someone elses code is very helpful. I bought your book by the way.

2. Works a treat! Thanks.

3. The Mouse driven events are things of beauty.
   I often use them to create drag and drop effects from one inbox to another.

4. Okay, Mike, ya got me interested. Do you have a nice example you could share?

5. While we are at it, let me share a resizable userform that I use in most of my work.

   Userform with Label1, Label2 and Label3. Setting label properties by code may or may not work, so setting Label3 to Marlett in designtime is a good idea. The code contains quite a few “greater than” symbols, I hope they come through OK. If not, replace junk with “greater than”.

   Option Explicit ‘********************************************

   Private Declare Function GetSystemMetrics Lib _
   “user32? (ByVal nIndex As Long) As Long
   Private Const mlSM_CYSIZE = 31

   Dim LStyle As Long
   Dim X1 As Long, Y1 As Long
   Dim Xmin As Long, Ymin As Long

   Function bIsXPStyle() As Boolean
   On Error Resume Next
   bIsXPStyle = (CLng(GetSystemMetrics(mlSM_CYSIZE)) >= 25)
   End Function

   Private Sub UserForm_Initialize()
   Xmin = 100 ‘minimum form width
   Ymin = 100 ‘minimum form height
   With Label1
   .BorderStyle = fmBorderStyleNone
   .BackStyle = fmBackStyleTransparent
   .Width = 6
   .Top = 0
   .MousePointer = fmMousePointerSizeWE
   .Caption = “”
   End With

   With Label2
   .BorderStyle = fmBorderStyleNone
   .BackStyle = fmBackStyleTransparent
   .Height = 6
   .MousePointer = fmMousePointerSizeNS
   .Caption = “”
   .Left = 0
   End With

   With Label3
   .BorderStyle = fmBorderStyleNone
   .BackStyle = fmBackStyleTransparent
   .Height = 22
   .Width = 22
   .MousePointer = fmMousePointerSizeNWSE
   .ForeColor = RGB(120, 120, 120)
   .Font = “Marlett”
   .TextAlign = fmTextAlignCenter
   .Caption = “p”
   End With
   ‘Xpstyle means higher form caption:
   If bIsXPStyle = True Then
   LStyle = 0
   Else
   LStyle = 6
   End If

   Call Form_resize
   End Sub

   Private Sub Label1_MouseDown(ByVal _
   Button As Integer, _
   ByVal Shift As Integer, _
   ByVal X As Single, ByVal Y As Single)
   X1 = X
   End Sub

   Private Sub Label1_MouseMove(ByVal Button As Integer, _
   ByVal Shift As Integer, _
   ByVal X As Single, ByVal Y As Single)
   If Button > 0 Then
   If (Me.Width + X – X1) > Ymin Then Me.Width = Me.Width + X – X1
   Call Form_resize
   End If
   End Sub

   Private Sub Label2_MouseDown(ByVal _
   Button As Integer, _
   ByVal Shift As Integer, _
   ByVal X As Single, ByVal Y As Single)
   Y1 = Y
   End Sub

   Private Sub Label2_MouseMove(ByVal _
   Button As Integer, _
   ByVal Shift As Integer, _
   ByVal X As Single, ByVal Y As Single)
   If Button > 0 Then
   If (Me.Height + Y – Y1) > Ymin Then Me.Height = Me.Height + Y – Y1
   Call Form_resize
   End If
   End Sub

   Private Sub Label3_MouseDown(ByVal _
   Button As Integer, _
   ByVal Shift As Integer, _
   ByVal X As Single, ByVal Y As Single)
   X1 = X
   Y1 = Y
   End Sub

   Private Sub Label3_MouseMove(ByVal _
   Button As Integer, _
   ByVal Shift As Integer, _
   ByVal X As Single, ByVal Y As Single)
   If Button > 0 Then
   If (Me.Width + X – X1) > Xmin Then Me.Width = Me.Width + X – X1
   If (Me.Height + Y – Y1) > Ymin Then Me.Height = Me.Height + Y – Y1
   Call Form_resize
   End If
   End Sub

   Private Sub Form_resize()
   Dim LngB As Long
   On Error Resume Next
   Label1.Left = Me.Width – 8
   Label1.Height = Me.Height – 28 + LStyle
   Label2.Top = Me.Height – 30 + LStyle
   Label2.Width = Me.Width – 24
   Label3.Top = Me.Height – 41 + LStyle
   Label3.Left = Me.Width – 24
   ‘code for form controls here, like
   ‘ListBox1.Height = Me.Height – ListBox1.Top – 58 + LStyle
   ‘ListBox1.Width = Me.Width – ListBox1.Left – 48
   ‘CommandButton1.Top = Me.Height – 48 + LStyle
   ‘CommandButton1.Left = Me.Width – 50
   Me.Repaint
   End Sub

   ‘************************************

   Yes, you must code top/left/height/width for each and every control. I find that work pretty relaxing myself, but it’s understandable if you don’t see it that way…

   Best wishes Harald

6. For those that are not familiar with “Marlett” it’s a fonttype, i e You change the font to Marlett for Label3.

   I’m lazy and therefore I use classic VB to create COM add-ins and I use third-party ActiveX controls to resizing controls and forms (when it’s necessary) ;)

   Kind regards,
   Dennis

7. Hi Stephen,

   Isn’t it amazing how easy it is to move a userform?

   That, essentially, is the code used in
   Excel calculator
   http://www.tushar-mehta.com/excel/software/utilities/xlcalculator.html

   When I made the calculator available I also meant to share the code I wrote for a marketing survey “proof of concept” add-in. It consisted of N preference slots numbered 1 to N where 1 was the “most preferred” and N the least. There were also N tokens representing products (say coffeemakers with different capacities, brewing speeds, automatic features, etc.). The person taking the survey had to ‘move’ the N product tokens into the N preference slots.

   The whole implementation was within a userform. There were 2 classes, one for the slots, the other for the products, which made everything very scalable since N could be easily changed. The code essentially came down to a few statements in the appropriate class so that each object could move itself. What I thought was a nice touch was a “snap” capability, whereby if one moved a product token so that it was “almost” inside a preference slot, it snapped into place.

   I’ll see if I can find that add-in. If so, I’ll share it together with the code that made it work.

8. For the past six years I’ve being using my CFormResizer class to do resizing for me. That uses resizing information included in each control’s Tag property, so the resize behaviour is specified as I’m laying out the form. ;-)

9. Hi Stephen

   I’m familiar with your class. I had some problems with it, can’t remember what it was now, but it created some serious conflict in some of my modeless forms. So I wrote the label thing.

   The tag idea is just brilliant.

   Dennis: Com and ActiveX’es are fun and powerful, but my users aren’t allowed to install anything -except xla’s. The IT people see Excel as a toy, so we can do anything with Excel files.

   Best wishes Harald

10. Hi Jon,

    I’ll throw something together for you. Since I can’t remember anyting, I’ll have to find one of my past project : )

11. Ok Jon,
    I’ve put together this example of how drag and drop can be simulated in Excel
    I have to admit that this technique is alot cleaner in Access.

    Get the example here:
    http://www.datapigtechnologies.com/downloads/Drag_Drop_Example.xls

12. Here’s an example using the Drag drop events.
    Stick two listboxes on a userform.

    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 ListBox2_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, _
    ByVal Data As MSForms.DataObject, ByVal X As Single, _
    ByVal Y As Single, ByVal DragState As MSForms.fmDragState, _
    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 MSForms.fmAction, _
    ByVal Data As MSForms.DataObject, ByVal X As Single, _
    ByVal Y As Single, _
    ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

    ‘ drop something

    Cancel = True
    Effect = 1

    ListBox2.AddItem Data.GetText

    End Sub
    Private Sub UserForm_Initialize()

    ListBox1.List = Array(“One”, “Two”, “Three”)

    End Sub

    Hope this code comes out cleanly.

13. Not too bad, when copy/pasting code you just need to sort out the single and double quotes.

14. Andy,

    Yours is alot cleaner. I like it!
    This is going in my code library.

15. Cool, I’ll have to try them both.

16. Very nice guys :)

    Harald – “The IT people see Excel as a toy…”

    I always get *very* annoyed when I hear an opinion like that. It limit the usability and do not leverage already made investments.

    Anyway, thanks for sharing some cool stuff!

    Kind regards,
    Dennis

17. I try not to get annoyed, the general lack of Office knowledge is very useful to me (re the Web application discussion). They deploy Office with macro security set to Low, no kidding.

    But there are times when I can’t do it. Once I mumbled “if you knew what I can do with this program then you’d ban it from our computers”. He looked at me with a confused smile and probably thought something like “so that’s what happens to a grownup when he spends all day playing with toys” :-)

    Best wishes Harald

18. I find things funny sometimes. Corporates dictate some aspects while other aspects are totally disregarded or even ignored. Security is one of them…

    Kind regards,
    Dennis

19. Harald…. I agree with you regarding the “gneral lack of knowledge on Office(and excel in particular)” and the fact that they dont think of Excel as a “serious” software….

    I also find it amusing and sad to see coorporates blow up enormous amount of money on software that they dont really need (eg ERP packages to hadle relatively low of trasacations)

    They dont bother to find out if the same process can be handled with a simple office application (excel/may be Access) with some customisation….

    The result is that they buy a sledge hammers to break a peanuts !

    Regards
    Sam

20. Isn’t the check for “1? in

    If Button And 1 Then

    redundant? Won’t “1? always be true, or am I missing something? It does work without it (If Button Then) so perhaps it could be removed?

    Besides that, it’s nice.

21. Johan,

    Try moving the form using the right button using Stephen’s version and your version… that’s what the 1 means, that the clicked button on the mouse is the left button.

22. Tushar Mehta wrote:

    “I’ll see if I can find that add-in. If so, I’ll share it together with the code that made it work.”

    Dear Tushar, would you mind sharing the add-in and the code? If not, and you can dig it up, I would be very interested in having a look at it. I’m trying to set up an add-in with related code, but I’m struggling. If e-mail would be more appropriate, I can send you my address. Thanks in advance.

    Wim Gielis
    Wigi at Ozgrid.com, Newsgroups, Helpmij.nl, …
    http://www.wimgielis.be (in Dutch)

23. can i use label mousemove instead userform to move the userform?

24. Exactly what I needed. Thanks so much!

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.