I have a userform that I've added to a spreadsheet that I've been using. It's
being used as a floating form. It can be moved anywhere on the form as the
user interacts with the spreadsheet. What I'd like to do is save the screen
position (top and left properties) when I close the form. So that the next
time the user opens the form it'll move back to the position it was in last
time.

So what I've done is I've coded the userform terminate event to write the
top and left properties to a sheet in the workbook. next time the form opens
it'll read those cells and move the form to the position saved.

Here's what I've got:

Private Sub UserForm_Terminate()

Sheets("Manning Config").Range("BU3").Value = Me.Left
Sheets("Manning Config").Range("BV3").Value = Me.Top

End Sub


The problem though is that it's writing zeros to the cells and not the
actual screen position where the form is sitting. Is the Me.Left and Me.Top
properties not the ones that I should be reading from?

Also, I haven't yet coded the form activate event yet. I'll have to have
some condition that will get the screen width and height and make sure that
the saved top and left values aren't larger than the available screen space
so that it doesn't try to open the form in a place where the user can't get
it. I think I already know how to do this part though.

Try using:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)



Jonathan Brown wrote:

I have a userform that I've added to a spreadsheet that I've been using. It's
being used as a floating form. It can be moved anywhere on the form as the
user interacts with the spreadsheet. What I'd like to do is save the screen
position (top and left properties) when I close the form. So that the next
time the user opens the form it'll move back to the position it was in last
time.

So what I've done is I've coded the userform terminate event to write the
top and left properties to a sheet in the workbook. next time the form opens
it'll read those cells and move the form to the position saved.

Here's what I've got:

Private Sub UserForm_Terminate()

Sheets("Manning Config").Range("BU3").Value = Me.Left
Sheets("Manning Config").Range("BV3").Value = Me.Top

End Sub

The problem though is that it's writing zeros to the cells and not the
actual screen position where the form is sitting. Is the Me.Left and Me.Top
properties not the ones that I should be reading from?

Also, I haven't yet coded the form activate event yet. I'll have to have
some condition that will get the screen width and height and make sure that
the saved top and left values aren't larger than the available screen space
so that it doesn't try to open the form in a place where the user can't get
it. I think I already know how to do this part though.

--

Dave Peterson

ps. If you step through your code (for the _terminate event), you can go back
to excel and see that the userform has disappeared before the first line of code
is executed.

Maybe that's why you see 0's in those cells.

Jonathan Brown wrote:

I have a userform that I've added to a spreadsheet that I've been using. It's
being used as a floating form. It can be moved anywhere on the form as the
user interacts with the spreadsheet. What I'd like to do is save the screen
position (top and left properties) when I close the form. So that the next
time the user opens the form it'll move back to the position it was in last
time.

So what I've done is I've coded the userform terminate event to write the
top and left properties to a sheet in the workbook. next time the form opens
it'll read those cells and move the form to the position saved.

Here's what I've got:

Private Sub UserForm_Terminate()

Sheets("Manning Config").Range("BU3").Value = Me.Left
Sheets("Manning Config").Range("BV3").Value = Me.Top

End Sub

The problem though is that it's writing zeros to the cells and not the
actual screen position where the form is sitting. Is the Me.Left and Me.Top
properties not the ones that I should be reading from?

Also, I haven't yet coded the form activate event yet. I'll have to have
some condition that will get the screen width and height and make sure that
the saved top and left values aren't larger than the available screen space
so that it doesn't try to open the form in a place where the user can't get
it. I think I already know how to do this part though.

--

Dave Peterson

Hi Jonathan,

By the time I worked out how to do this I see that Dave has already provided
what is probably a solution but thought I would post what I have discovered
anyway. You can use UserForm_Layout event to save the parameters.

To reset the form to the last used position you need to set the forms
StartupPosition property to 0 - Manual and then the code in the Initialize
event as I have written it.

For some reason I was not able to code the Move command using the named
parameters of Left:=, Top:= etc because I kept getting an error but simply
separating the values with a comma and placing them in the correct default
order it works. Perhaps someone can comment on this.


Private Sub UserForm_Layout()
'Saves the forms position, height and width.
Sheets("Sheet1").Range("A1") = Me.Left
Sheets("Sheet1").Range("A2") = Me.Top
Sheets("Sheet1").Range("A3") = Me.Width
Sheets("Sheet1").Range("A4") = Me.Height
End Sub

Private Sub UserForm_Initialize()
'NOTE: Set form property StartupPosition
'to 0 - Manual
'Order of parameters in code is
'Left, Top, Width, Height

UserForm1.Move _
Sheets("Sheet1").Range("A1"), _
Sheets("Sheet1").Range("A2"), _
Sheets("Sheet1").Range("A3"), _
Sheets("Sheet1").Range("A4")

End Sub


Regards,

OssieMac

Thanks Dave! That was the answer that I needed. It works perfectly now.

"Dave Peterson" wrote:

Try using:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)



Jonathan Brown wrote:

I have a userform that I've added to a spreadsheet that I've been using. It's
being used as a floating form. It can be moved anywhere on the form as the
user interacts with the spreadsheet. What I'd like to do is save the screen
position (top and left properties) when I close the form. So that the next
time the user opens the form it'll move back to the position it was in last
time.

So what I've done is I've coded the userform terminate event to write the
top and left properties to a sheet in the workbook. next time the form opens
it'll read those cells and move the form to the position saved.

Here's what I've got:

Private Sub UserForm_Terminate()

Sheets("Manning Config").Range("BU3").Value = Me.Left
Sheets("Manning Config").Range("BV3").Value = Me.Top

End Sub

The problem though is that it's writing zeros to the cells and not the
actual screen position where the form is sitting. Is the Me.Left and Me.Top
properties not the ones that I should be reading from?

Also, I haven't yet coded the form activate event yet. I'll have to have
some condition that will get the screen width and height and make sure that
the saved top and left values aren't larger than the available screen space
so that it doesn't try to open the form in a place where the user can't get
it. I think I already know how to do this part though.

--

Dave Peterson