ExcelBanter - Terminating Forms

How about not displaying the X in the corner and thus requiring the user to
close the form via a button that has the statement Me.Hide in it? This
retains the userform in memory. The X unloads it from memory. code to hide
the X is:

'this goes at the top of the module:

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Const GWL_STYLE = (-16)
Const WS_SYSMENU = &H80000

use in userform:

Private Sub UserForm_Initialize()
'this hides the X on the caption line
Dim hWnd As Long, a As Long

Dim V As Integer
V = CInt(Left(Application.Version, _
InStr(Application.Version, ".")))
'If V = 8 this is Excel 97
If V = 8 Then
hWnd = FindWindow("ThunderXFrame", Me.Caption)
Else
hWnd = FindWindow("ThunderDFrame", Me.Caption)
End If
a = GetWindowLong(hWnd, GWL_STYLE)

SetWindowLong hWnd, GWL_STYLE, a And Not WS_SYSMENU
End Sub

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"S. L. S." <S. L. wrote in message
...
Hello,

I have set up VBA code so that when I terminate a form (press the little X
in the corner) it executes a set of code then reopens the form. This part
is
working fine, but when I reopen the form, nothing on the form will work.
The
buttons will not respond when clicked and the form cannot be terminated or
close without breaking the code.

I have tried running the form by using the Show function. I have also
Unloaded and Loaded the form prior to running the Show function and
nothing
seems to help.

Any ideas would be appreciated.

Thanks

S.S.