Locate where my Form terminates

[southfarm]

Hi,

Hi I'm trying to find the VBA code for determine where I terminate my form so when I initiate it again it will be located at the same position. I want to save the position into the value of a cell. I've tried:

Private Sub UserForm_Terminate()
Range("xPos").Value = Me.Left
Range("yPos").Value = Me.Top
End Sub

But it only returns 0,0 (default in the form properties)

Thank you for any help!

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

Try this, instead:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Range("xPos").Value = Me.Left
    Range("yPos").Value = Me.Top
End Sub

The problem with the Terminate event is that the UserForm has already "gone" by then so it has no position.


Regards,

 

[southfarm]

Ok. That makes sense. Is there any way one can collect the position every time it's getting moved to a new location? That would give me the same result!

 

[HackSlash]

There is a solution posted elsewhere in this very forum!!!

[COLOR=#333333]Private Sub UserForm_Activate()    [/COLOR][INDENT]Me.Top = [yPos][/INDENT]
[INDENT]Me.Left = [xPos][/INDENT]
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Not [yPos] = Me.Top Or Not [xPos] = Me.Left Then
        [yPos] = Me.Top
        [xPos] = Me.Left        
    End If
[COLOR=#333333]End Sub
[/COLOR]

I modified it to match your named ranges. It might even work!

http://www.mrexcel.com/forum/excel-questions/671108-userform-startup-position.html

 

[southfarm]

I'm very very sorry, but I have been searching for 1-2 hours before I posted my question I'm just doing the best I can.

 

[HackSlash]

It's okay.... did it work?

 

[southfarm]

Yes - it did - Also managed to find the solution in the thread you shared
Thank you very much!