We’re all used to moving a form around the screen by dragging the title bar. But what if you’ve used some API calls to remove the title bar from the form? The answer is to add a little bit of VBA that allows the user to move the form by dragging within the form area itself:
Dim mdOriginX As Double
Dim mdOriginY As Double
‘Store where we started
Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
mdOriginX = X
mdOriginY = Y
End Sub
‘Move the form as the mouse moves with the left button down
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button And 1 Then
Me.Left = Me.Left + (X – mdOriginX)
Me.Top = Me.Top + (Y – mdOriginY)
End If
End Sub
This code can also be easily adapted to allow users to drag controls around the form at run-time.
Enjoy,
Stephen Bullen
I’m new to VBA so thanks! Reading someone elses code is very helpful. I bought your book by the way.
Works a treat! Thanks.
The Mouse driven events are things of beauty.
I often use them to create drag and drop effects from one inbox to another.
Okay, Mike, ya got me interested. Do you have a nice example you could share?
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
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
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.
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. ;-)
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
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 : )
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
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.
Not too bad, when copy/pasting code you just need to sort out the single and double quotes.
Andy,
Yours is alot cleaner. I like it!
This is going in my code library.
Cool, I’ll have to try them both.
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
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
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
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
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.
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.
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)
can i use label mousemove instead userform to move the userform?
Exactly what I needed. Thanks so much!