Disallowing "x" to terminate form 1

[Kaulana]

Using VBA in Excel, would like to deactivate the "x" in the upper right of forms so that user "must" use the "Close" button to close the form. Any way to do that? Kaulana (aka Bob)

http://www.bobfarley.com

 

[Kaulana]

Never mind. I found the answer, or at least one answer. It's the UserForm_QueryClose procedure. It has a Cancel As Integer bit that, if you set Cancel = True or -1(?) then it causes the x button not to work. I put a msgbox in it, too, to let the user know to use the Close button. Kaulana (aka Bob)

http://www.bobfarley.com

 

[logius]

There, ya get a star for figuring it out. Feel like we're back in grade school yet? =)

 

[vbMax]

Be aware that users can often bypass a
Close button by pressing ALT-F4.

 

[Guest_imported]

I've inheritted a module that actually disables the "X" or which ever action you wish to disable through the actual operating system. This is a much more reliable solution.

you'll have to weave through this.......

Option Private Module

Option Explicit

'-----------------------------Declarations to Remove Dialog Controls
Private Const MfByPosition As Long = &H400 'Deletes the menus by position (this is our default).
Private Const MinimumSysMenuItems As Long = 9 'This is the number of items on the system menu
Private Const SW_NORMAL As Long = 1

Private Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

'--------------Set Dialog to Top API---------------
Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Declare Function GetWindowPlacement Lib "user32" (ByVal hwnd As Long, lpwndpl As WINDOWPLACEMENT) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long

'--------------Remove Dialog Controls API----------
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

'--------------Get Class Name----------------------
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Private Type POINTAPI
x As Long
y As Long
End Type

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Private Type WINDOWPLACEMENT
Length As Long
Flags As Long
showCmd As Long
MinPosition As POINTAPI
MaxPosition As POINTAPI
NormalPosition As RECT
End Type



'Purpose : Deletes the system control menu of the specified window.
'Inputs : DialogCaption as String = The caption of the window whose control menu you want to delete.
'Outputs : N/A
'Author : Andrew Baker
'Date : 30/05/2000
'Notes : Call this sub during the Initialize Event of the Dialog
' Disables all the dialog menus including the min, max, terminate and
' resizing.
' To Call in VBA use:
' DialogDisableMenuControls Me.Caption
' Control 6 contains the Terminate (X) control.
'Revisions :

Sub DialogDisableMenuControls(DialogCaption As String)
Dim lHandle As Long, lCount As Long

On Error Resume Next
lHandle = GetDialogHwnd(DialogCaption)
' Only continue if the passed window handle isn't zero.
If lHandle <> 0 Then
' There are 9 items on the application control menu.
' Loop through and disable each one.
'For lCount = 1 To MinimumSysMenuItems
'The nPosition of the DeleteMenu function will always be 0,
'because as we delete each menu item, the next one moves up
'into the first position (index of 0).
DeleteMenu GetSystemMenu(lHandle, False), 6, MfByPosition
'Next
End If
On Error GoTo 0
End Sub

'Purpose : Returns the Windows Handle of a Dialog based on its caption.
' Find windows scans down the Z Order of the dialogs currently displayed,
' before returning the handle of the matching dialog (use Spy ++ to find the class names).
'Inputs : DialogCaption as String
'Outputs : The Dialogs Window Handle
'Author : Andrew Baker
'Date : 30/05/2000
'Notes : To Call in VBA use
' lHwnd = GetDialogHwnd(Me.Caption)
'Revisions :
Function GetDialogHwnd(ByVal DialogCaption As String) As Long
On Error Resume Next
GetDialogHwnd = FindWindowA(vbNullString, DialogCaption)
On Error GoTo 0
End Function