I have a push button on my spreadsheet which loads my Userform
in the following manner:

Sub Button1_Click()
UserForm1.Show (vbModeless) ' Display my Userform
End Sub

When a user presses the "X" button on the Userform object
I believe that Excel will terminate the Userform instead of
simply "hiding" it by making it invisible. How can I program
my Userform to turn invisible instead of terminating itself??

I would rather have the Userform turn invisible so it can
maintain its current position on the screen when it is
re-activated again..... I noticed that the Userform position
gets reset to default after the form is terminated, and I want
to avoid this.

Thank you!

You can hide your form and maintain it in memory until Show'n again. However
if the only reason is to display next time at the previous position far
better to trap the coordinates before unloading, and reapply them when the
form reloads. Try something like this

' normal module
Public gFormLeft As Single
Public gFormTop As Single

Sub test()
UserForm1.Show vbModeless

End Sub

' userform module

Private Sub UserForm_Initialize()

If gFormLeft Or gFormTop Then
With Me
.StartUpPosition = 0
.Left = gFormLeft
.Top = gFormTop
End With
End If

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)

With Me
gFormLeft = .Left
gFormTop = .Top
End With

End Sub

Instead of saving the position to variables you could save them to hidden
cells, or even pop them in the registry (see SaveSetting and GetSetting).

Regards,
Peter T


"Robert Crandal" wrote in message
...
I have a push button on my spreadsheet which loads my Userform
in the following manner:

Sub Button1_Click()
UserForm1.Show (vbModeless) ' Display my Userform End Sub

When a user presses the "X" button on the Userform object
I believe that Excel will terminate the Userform instead of
simply "hiding" it by making it invisible. How can I program
my Userform to turn invisible instead of terminating itself??

I would rather have the Userform turn invisible so it can
maintain its current position on the screen when it is
re-activated again..... I noticed that the Userform position
gets reset to default after the form is terminated, and I want
to avoid this.

Thank you!

Good, your code is exactly what I was looking for.....

I just have one question about the variables "gFormLeft"
and "gFormTop". The code works great when I place
these variables in the "normal module", but it does NOT
work if I move the variables into the "userform module".
Do you know why this code would not work if I put
these variables into the userform module??? just curious...

It might be a newbie question! 8)

"Peter T" <peter_t@discussions wrote in message
...

You can hide your form and maintain it in memory until Show'n again.
However if the only reason is to display next time at the previous
position far better to trap the coordinates before unloading, and reapply
them when the form reloads. Try something like this

' normal module
Public gFormLeft As Single
Public gFormTop As Single

Sub test()
UserForm1.Show vbModeless

End Sub

' userform module

Private Sub UserForm_Initialize()

If gFormLeft Or gFormTop Then
With Me
.StartUpPosition = 0
.Left = gFormLeft
.Top = gFormTop
End With
End If

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)

With Me
gFormLeft = .Left
gFormTop = .Top
End With

End Sub

Instead of saving the position to variables you could save them to hidden
cells, or even pop them in the registry (see SaveSetting and GetSetting).

Regards,
Peter T

A userform is a type of Class object that only "exists" when while it is
loaded. That occurs as soon as you refer to it in code, eg with
Userform1.Show. When the form is unloaded it exists no more and any module
level immediately loose scope.

That's a somewhat simplistic explanation, but basically why Userform
variables are lost when the form is destroyed (unloaded).

Regards,
Peter T



"Robert Crandal" wrote in message
...
Good, your code is exactly what I was looking for.....

I just have one question about the variables "gFormLeft"
and "gFormTop". The code works great when I place
these variables in the "normal module", but it does NOT
work if I move the variables into the "userform module".
Do you know why this code would not work if I put
these variables into the userform module??? just curious...

It might be a newbie question! 8)

"Peter T" <peter_t@discussions wrote in message
...

You can hide your form and maintain it in memory until Show'n again.
However if the only reason is to display next time at the previous
position far better to trap the coordinates before unloading, and reapply
them when the form reloads. Try something like this

' normal module
Public gFormLeft As Single
Public gFormTop As Single

Sub test()
UserForm1.Show vbModeless

End Sub

' userform module

Private Sub UserForm_Initialize()

If gFormLeft Or gFormTop Then
With Me
.StartUpPosition = 0
.Left = gFormLeft
.Top = gFormTop
End With
End If

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)

With Me
gFormLeft = .Left
gFormTop = .Top
End With

End Sub

Instead of saving the position to variables you could save them to hidden
cells, or even pop them in the registry (see SaveSetting and GetSetting).

Regards,
Peter T

Robert Crandal;566072 Wrote:
I have a push button on my spreadsheet which loads my Userform
in the following manner:

Sub Button1_Click()
UserForm1.Show (vbModeless) ' Display my Userform
End Sub

When a user presses the "X" button on the Userform object
I believe that Excel will terminate the Userform instead of
simply "hiding" it by making it invisible. How can I program
my Userform to turn invisible instead of terminating itself??

I would rather have the Userform turn invisible so it can
maintain its current position on the screen when it is
re-activated again..... I noticed that the Userform position
gets reset to default after the form is terminated, and I want
to avoid this.

Thank you!

To answer your original question, put this code in the user form's code
modlue:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseMode = 0 Then '(closemode = 0 means x in top right was
clicked)
Cancel = True
Me.Hide
End If
End Sub

The form still exists, controls values too, for when -.show- gets
executed again


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=156259

Microsoft Office Help