[RESOLVED] Excel: Detect movement of UserForm

Printable View

Sep 11th, 2009, 12:17 PM
VBAhack

[RESOLVED] Excel: Detect movement of UserForm

Is there any way to detect that a modeless UserForm has been moved by a mouse click/drag in the title area? UserForm_MouseMove and UserForm_MouseUp appear to work only with buttons.

When a Userform is moved, it comes in focus. I want to detect the move and somehow remove the focus.
Sep 11th, 2009, 01:13 PM
Siddharth Rout

Re: Excel: Detect movement of UserForm

Quote:

When a Userform is moved, it comes in focus. I want to detect the move and somehow remove the focus.

Do you mean you don't want the user to move the userform?
Sep 11th, 2009, 02:15 PM
VBAhack

Re: Excel: Detect movement of UserForm

No, it's related to my post from yesterday.
http://www.vbforums.com/showthread.php?t=583746
The userform is displayed when a cell is clicked. The intent is to fill the cell by picking an item from a listbox within the userform. But, while the userform is displayed I want keyboard keys like PgUp and the arrows keys to function normally. I've figured out how to do this by using AppActivate ("Microsoft Excel") whenever the userform is displayed, which changes the focus from the userform back to the sheet cell. So far so good.

Now, if the userform is moved, the focus moves back to the userform, which means pressing PgUp or an arrow key selects an item in the listbox. I want to restrict listbox selection to mouse only. My thought was that by detecting the movement of the userform, I can once again use AppActivate ("Microsoft Excel") to set the focus back to the sheet.

Hope this makes sense.
Sep 11th, 2009, 03:06 PM
Siddharth Rout

Re: Excel: Detect movement of UserForm

I have been fighting with the mouse API's for the last 2 hrs but I am still stuck...

There is one option though.. Creating a userform without the title bar... Then I guess it would be easy to achieve what you want...
Sep 11th, 2009, 03:18 PM
VBAhack

Re: Excel: Detect movement of UserForm

Hmmmm. Which mouse API's?
Sep 11th, 2009, 03:20 PM
Siddharth Rout

Re: Excel: Detect movement of UserForm

Quote:

There is one option though.. Creating a userform without the title bar... Then I guess it would be easy to achieve what you want...

Yes! i tried it and it is possible... let me know if you are interested...
Sep 11th, 2009, 03:24 PM
VBAhack

Re: Excel: Detect movement of UserForm

I need to keep the titles. What mouse API's were you toying with?
Sep 11th, 2009, 03:26 PM
Siddharth Rout

Re: Excel: Detect movement of UserForm

Quote:

Originally Posted by VBAhack

I need to keep the titles. What mouse API's were you toying with?

http://www.google.co.in/#hl=en&sourc...bff82df9e80f1a

Btw you can always use the labels to show the title
Sep 11th, 2009, 03:39 PM
VBAhack

Re: Excel: Detect movement of UserForm

I use the caption property of the userform to display the title. Is that what you mean by label?
Sep 11th, 2009, 03:43 PM
Siddharth Rout

Re: Excel: Detect movement of UserForm

yes. would you like to see what I have got?
Sep 11th, 2009, 03:49 PM
VBAhack

Re: Excel: Detect movement of UserForm

Seem to be running out of possibilities........

1. There's no event associated with clicking in the caption area of a userform or for changing the position of the userform

2. There doesn't appear to be a mouse event API that fires when a mouse is clicked or moved. I can get the mouse position via API and simulate a mouse click, but not detect a mouse click or movement.

3. There's no "before key press" event
Sep 11th, 2009, 03:50 PM
VBAhack

Re: Excel: Detect movement of UserForm

Quote:

Originally Posted by koolsid

yes. would you like to see what I have got?

Sure, I'd be interested. Might learn something. Thanks.
Sep 11th, 2009, 04:04 PM
Siddharth Rout

1 Attachment(s)

Re: Excel: Detect movement of UserForm

Amend it as per your requirements...

At the moment it is not beautiful... I leave the beautification to you... Also I have not commented the code so if you get stuck then let me know...

I feel devilish after making this :lol: Hope you like it as much I liked creating it :D
Sep 11th, 2009, 09:40 PM
westconn1

Re: Excel: Detect movement of UserForm

try userform layout event
Sep 11th, 2009, 10:01 PM
Siddharth Rout

Re: Excel: Detect movement of UserForm

Yeah Pete, I tried that as well last night. The only Problem is that this event doesn't wait for me to finish my dragging. It simply runs when I am holding the title bar (with the mouse of course :))

Re: Excel: Detect movement of UserForm

could use some sort of timer to wait for the event to stop firing for some length time

i tried like
vb Code:
Private Sub UserForm_Layout()
If Not wait Then
wait = True
mleft = Left
Application.OnTime Now + TimeValue("00:00:01"), "unwait"
Debug.Print Me.Left
End If
End Sub
 
' in a module
 
Public wait As Boolean
Public mleft As Integer
 
Public Sub unwait()
MsgBox "moved " & Abs(UserForm1.Left - mleft)
wait = False
End Sub
it fired on activate, which i am sure could be avoided

Sep 14th, 2009, 01:01 PM
VBAhack

Re: Excel: Detect movement of UserForm

Quote:

Originally Posted by westconn1

try userform layout event

Layout event does the trick! Thanks westconn1! I don't really care if it fires a couple of times.

Code:

Private Sub UserForm_Layout() AppActivate ("Microsoft Excel") End Sub

Thanks koolsid, that was some pretty clever work! Substituting a label button for the title and then capturing the mouse move event. That approach has a desireable side effect that the button to dismiss the form can easily be hidden.