[RESOLVED] Excel: Detect movement of UserForm

[VBAhack]

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.

[Siddharth Rout]

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?

[VBAhack]

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.

[Siddharth Rout]

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...

[VBAhack]

Hmmmm. Which mouse API's?

[Siddharth Rout]

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...

[VBAhack]

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

[Siddharth Rout]

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

[VBAhack]

I use the caption property of the userform to display the title. Is that what you mean by label?

[Siddharth Rout]

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

[VBAhack]

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

[VBAhack]

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

Sure, I'd be interested. Might learn something. Thanks.

[Siddharth Rout]

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 Hope you like it as much I liked creating it

[westconn1]

try userform layout event

[Siddharth Rout]

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 )

[westconn1]

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

i tried like

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

[VBAhack]

try userform layout event

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

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.