Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SIMPLEST way to disable "close" button on form?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SIMPLEST way to disable "close" button on form?
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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "codytheretriever" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SIMPLEST way to disable "close" button on form?
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 "codytheretriever" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SIMPLEST way to disable "close" button on form?
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 codytheretriever wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SIMPLEST way to disable "close" button on form?
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 -- HTH Bob Phillips "codytheretriever" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable dropdown list (Combo box -"Form control") | Excel Worksheet Functions | |||
Disable "save" option on close. | Excel Discussion (Misc queries) | |||
Folder Graphic (in Word & Pub) instead of "Close" button in Excel | Excel Discussion (Misc queries) | |||
Changing data source in pivot table- "Back" button is disable | Excel Discussion (Misc queries) | |||
Need a "save & close" button directly on spreadsheet | Excel Discussion (Misc queries) |