Disable the Close button to ensure your dialog box gets the attention it needs

When you create a dialog box with VBA, it’s frustrating to think that anyone can dismiss it in one click. This means that the hard work you spent on creating a dialog box that serves an important purpose (often to the user’s advantage!) goes to waste. For instance, in the accompanying article “Tailor your workbook’s data while ensuring a clean slate for coworkers,” a colleague can sidestep the dialog box we create by clicking the red Close button.

You can stop this by disabling the all-too-familiar red Close button in the upper-right corner of the dialog box. Every time you create a UserForm, this button is automatically included in the title bar. However, you can intercept it with a message box.
To disable the Close button in a dialog box:

1. Launch Excel and open a workbook in which you’ve created a dialog box.
2. Press [Alt][F11] ([option][F11] in 2004) to open the VBE (Visual Basic Editor), and then right-click ([control]-click in 2004) on the UserForm in your Project Explorer. (If it isn’t displayed, choose View | Project Explorer from the VBE’s menu bar.)
3. Choose View Code from the resulting shortcut menu.
4. Enter the code shown below in the code window.

Private Sub UserForm_QueryClose (Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    MsgBox "Please make a selection and click OK."
    Cancel = True
End If
End Sub
We chose to alert users to Please make a selection and click OK. Of course, you can change the message box’s prompt to suit your own needs. You may want it to simply read Close button disabled. However, it’s a good idea to give the user direction whenever you get the chance.

To test the disabled Close button:

1. Save the changes you’ve made in the VBE.
2. Access the UserForm as if you were any user. For example, we need to close the workbook and open it again to display our UserForm.
3. Click the Close button in the upper-right corner of the dialog box to see your message box display. The UserForm remains open.