Disable _Exit event when quitting userform using "Cancel" or "X-button"

Question

I've got a code in a dropdown box on my userform. Every time the user moves away from the drop down box, the code checks whether the value put by the user is correct (i.e. matches a list). If it doesn't, it triggers a message box. Here is my code:

Private Sub CmboxModifyRoute_Exit(ByVal Cancel As MSForms.ReturnBoolean) UserValue = CmboxModifyRoute.Value counter = 0 Cell = Range("C15").Value If UserValue = "" Then Exit Sub Do While (counter < 35 And Cell <> UserValue) 'checking if UserValue is valid counter = counter + 1 Cell = Range("C15").Offset(counter, 0).Value Loop If counter > 34 Then 'if invalid, then display message box MsgBox "Invalid", vbExclamation End If End Sub

The problem occurs when I quit the userform with the "X" button or "Cancel" button. If the UserValue is invalid, it still shows me the "Invalid" message box after I have already quit the userform. I don't want it, I just want the userform to unload. How can I deal with this? Many thanks!

Mathieu Guindon · Accepted Answer · 2015-03-13 15:33:33Z

Change your condition to this:

If Me.Visible And counter > 34 Then MsgBox "Invalid", vbExclamation End If

Then the message will not be displayed if the form isn't visible.

RubberDuck · Accepted Answer · 2015-03-13 15:34:36Z

Data Validation should go in the BeforeUpdate event of the combo box. Before Update won't trigger prior to the User Form's Terminate event. Add UserForm_Terminate and CmboxModifyRoute_BeforeUpdate events to your code, set breakpoints on the declaration of each, and watch the order of events happen in debug mode.

Private Sub CmboxModifyRoute_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'data validation goes here 'doesn't fire when the form is closed End Sub Private Sub CmboxModifyRoute_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'this triggers before Terminate End Sub Private Sub UserForm_Terminate() End Sub

Collectives™ on Stack Overflow

Disable _Exit event when quitting userform using "Cancel" or "X-button"

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related