Disabling textbox exit events when closing a userform

[indyman]

Maybe I am not using the correct search terms, but I am unable to find an answer to this design issue. I have a userform (several in fact) that have a multicolumn list for records related to that form. I have a good amount of validation that I run on some fields utilizing the Cancel function within the BeforeUpdate or Exit event of the textbox. I do this to provide immediate feedback and show detailed error messages - not letting them out of the field until the error is resolved - rather than wait for the full record save to do the error checking. But this design approach is causing issues.

Here is a specific scenario. Textbox1 has a date in it, which I check for bad date format and a few other things on the BeforeUpdate event. The user clicks on the first record in the list, which loads the saved date into Textbox1. No issues. Now the user deletes the entry in Textbox1 and decides to close the form without exiting the field. The user's changes are irrelevant to me as they have not been saved and will not be saved with closing the form. However Excel runs the BeforeUpdate event on Textbox1 immediately upon the close button being clicked. I get a Type Mismatch error because part of my validation is doing math on that date, which it now see as empty. And it does this before it even gets to the close function. It may help for me to note that I have the title bar and "X" hidden on these forms and am controlling closing of the form with my own button. But the same behavior exists with the Excel close "X".

Any ideas on how I can design around this? Am I going to have to move all the data validation to my Save function and skip the BeforeUpdate and Exit events? I am hoping I am just focusing too narrowly and not seeing a better way.

 

[Rick Rothstein]

You could create a global Boolean variable which will automatically default to False when the workbook is opened. You could then set it to True in the BeforeClose event and in the procedures that you do not want to run upon closing, put a test in for that variable at the beginning of the procedure and if True, exit the subroutine right then and there.

 

[indyman]

You could create a global Boolean variable which will automatically default to False when the workbook is opened. You could then set it to True in the BeforeClose event and in the procedures that you do not want to run upon closing, put a test in for that variable at the beginning of the procedure and if True, exit the subroutine right then and there.

@Rick Rothstein Thanks for the reply. But the BeforeClose event is a workbook level event. I need this to happen at the userform level, and need to bypass textbox beforeupdate or exit events if they are closing the form.

 

[Rick Rothstein]

Sorry, I forgot this was for a UserForm... same concept, different locations. Dim the Boolean variable I talked about at the top of your UserForm code window (outside of any procedures) and set the variable to True in the UserForm's QueryClose event procedure. Then put your test for this variable in the TextBox's Exit event procedure.

 

[indyman]

Thanks @Rick Rothstein After posting my reply earlier, I continued my searching, and found this post as well eventually got me there. Unwanted Textbox Exit event triggering with userform closing. I'm not sure why it did not appear as part of my original searches. Anyway, that post seems to address other question, and your answer is indeed succinct and does indeed work. But in short, here is my step by step.

I set a global boolean variable on userform called

bStopUpdateEvents

and then set it to False in the

UserForm_Initialize

.

I added this event to the userform:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bStopUpdateEvents = True
    Unload Me
End Sub

Then I simply add this line to each of my validation events:

If bStopUpdateEvents = True Then Exit Sub

After some quick testing it seems to work on

BefroreUpdate

,

AfterUpdate

, and

Exit

events.

 

[mikerickson]

You could change your TextBook validation to and Information style rather than a Stop style.
If the user enters a non-date, the user would be notified and have the option to continue with the bad data or correct the error.
Then when exitiing a UF with bad data, the user could choose to continue exiting without correction.

 

[indyman]

You could change your TextBook validation to and Information style rather than a Stop style.
If the user enters a non-date, the user would be notified and have the option to continue with the bad data or correct the error.
Then when exitiing a UF with bad data, the user could choose to continue exiting without correction.

@mikerickson I did consider that approach. But then I have to move all validation to the record Add or Save procedure. I've seen various discussion about data validation at the field level vs record save. There are pros and cons for each. I just prefer the field by field validation for my projected user base. And setting the boolean for the top events check works great.

 

[mikerickson]

What kind of Validation are you doing? Some requiremements like "enter only numbers" can be done more gracefully than with the Exit event.

 

[indyman]

What kind of Validation are you doing? Some requiremements like "enter only numbers" can be done more gracefully than with the Exit event.

Understood. I use Keypress for that. Other validations I do are looking for blank required fields, validate things to be within a range of numbers or years, etc..