spot_img

How To Disable Close Button Userform In Excel VBA

In this Article I will show you how to disable close button in Excel userform.

Let us first look at the close button we use in Excel userforms:

close useform

The red button at the top end of userform is the close button. Sometimes we want to disable this button so that the user can only close the userform from the exit button which is already created in the userform for the same purpose.

Lets start with the code to disable the close button:

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

If CloseMode = vbFormControlMenu Then 
    Cancel = True
    MsgBox "Please use the Exit button to close the form", 
vbOKOnly

End If

End Sub

We have to use this code along with the code of the existing userfrom:

vba code useform close

When we use this code and we try to press the close button it will prompt a message like shown below:

useform msgbox message

This Message can be changed in the following code line:

MsgBox “Please use the Exit button to close the form”, vbOKOnly

The propose for creating this functionality is that sometimes we have other conditions before exiting the doc like:

The user must enter data before closing.

On clicking the Exit button:

please enter name before exiting

For this function we want to disable the close button.

Template

You can download the Template here – Download
spot_img
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell