Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Terminating Forms
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you terminate an autorecover save operation w/o terminating EX | Excel Discussion (Misc queries) | |||
RefEdits and normal forms / forms in a DLL | Excel Programming | |||
Forms that open from forms | Excel Programming | |||
Calling Forms from Forms - Exit problems | Excel Programming | |||
VBA forms | Excel Programming |