DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

1

Prevent a userfrom from closing when the user clicks the x-button by using VBA

#preventauserfromfrom #vbamacros

In this tutorial, you will learn how to prevent an userfrom from closing when the user clicks the X-Button by using VBA ** in Microsoft Excel. Let’s see them below!! Get the official version of **MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

  • Firstly, you need to create an userform which can be activated by clicking the run button on the worksheet.

Sample data

  • In the Excel Worksheet, you have to go to the Developer Tab.
  • Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic
Select Visual Basic

  • Now, you have to copy and paste the code given below. 
Option Explicit
Sub running()
UserForm1.Show
End Sub

'Add below code in userform
Private Sub CommandButton1_Click()
'Close the userform
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Using Query Close event of Userform
'Comparing the constant value of CloseMode variable
'If it is equal to inbuilt constant of control menu
'Then prevent closing of userform and
'Display information message dialog box
If CloseMode = vbFormControlMenu Then  
    'Changing Cancel variable value to True
    'By default, it is False
    Cancel = True
    MsgBox "You can't close the dialog like this!"
End If
End Sub
Enter fullscreen mode Exit fullscreen mode
  • After that, you need to save the code by selecting it and then close the window.

Save the Code

  • Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
  • You need to choose the Macros option in the Code section.

Choose Macro option
Choose Macro option

  • Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

  • Finally, you will receive the output as an information message in the dialog box in Excel.

Sum-Up

In the above short tutorial, we have illustrated ** ** how to prevent an userfrom from closing when the user clicks the X-Button by using VBA in Microsoft Excel. Hope you like this article. Please feel free to state your query or feedback for the above article. Thank you so much for Reading!! To learn more, check out Geek Excel!! *And Excel Formulas *!!

Read Next:

Top comments (0)