SIMPLEST way to disable "close" button on form?

  • Thread starter Thread starter codytheretriever
  • Start date Start date
C

codytheretriever

Have 10 forms in an Excel based
project. On eight of the forms
I need to disable "close" button
and double-click capability so
that user must only go in directions
allowed by form command buttons.
On the other two forms using "close"
or double-click is ok. Have tried
all of the Knowledge Base suggestions
but keep getting compile errors for
"ambiguous ... blah ... blah ... blah",
NOT due to typing errors! Could someone
give me SIMPLE directions on how to
disable "close" button on a form by
form basis? Hope someone can help,
and thanks in advance if you can.
 
Try this in the Userform

Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
' Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
MsgBox "Clicking the Close button does not work."
Cancel = True
End If
End Sub
 
Put this in the code window for the form's you want close disabled on

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox Prompt:= "Close disabled"
End If
End Sub

Disable double clicking (form only not controls)
Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
MsgBox Prompt:="Double click disabled"
End Sub


Dan E
 
cody,

You can place the following code into each of the
UserForms:

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

' Disable the "X" on the userform so that the user can't
' dismiss the userform by clicking on the "X" in the upper
' right hand corner of the userform
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox Prompt:="Please use the buttons on the" & vbCrLf & _
" Form to Close it"
End If
End Sub

The above will still allow the user to break out if the code with
the Ctrl + Break key.
The following code (in the Workbook_Open Event or a regular
sub called before you open the UserForm) will cure that:

Application.EnableCancelKey = xlDisabled

John
 
Cody,

Use the form's QueryClose event. This has 2 arguments, Cancel and CloseMode.
CloseMode tells you where the close originates from.

vbFormControlMenu 0 The user has chosen the Close command from the
Control menu on the UserForm.
vbFormCode 1 The Unload statement is invoked from code.
vbAppWindows 2 The current Windows operating environment session is
ending.
vbAppTaskManager 3 The Windows Task Manager is closing the
application.


You can test this, and if it's from an unload statement, close else cancel

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Cancel = CloseMode <> vbFormCode And CloseMode <> vbAppWindows
End Sub
 
Back
Top