Hiding a "Userform" instead of closing it???

[Robert Crandal]

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!

 

[Peter T]

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]

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]

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

 

[p45cal]

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 cod
modlue:

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

The form still exists, controls values too, for when -.show- get
executed agai