Disabling textbox exit events when closing a userform

indyman

New Member
Joined
Apr 14, 2021
Messages
43
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,546
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Apr 14, 2021
Messages
43
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,546
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Solution

indyman

New Member
Joined
Apr 14, 2021
Messages
43
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
bStopUpdateEvents
and then set it to False in the
VBA Code:
UserForm_Initialize
.

I added this event to the userform:
VBA Code:
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:
VBA Code:
If bStopUpdateEvents = True Then Exit Sub

After some quick testing it seems to work on
VBA Code:
BefroreUpdate
,
VBA Code:
AfterUpdate
, and
VBA Code:
Exit
events.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,079
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

New Member
Joined
Apr 14, 2021
Messages
43
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,079
What kind of Validation are you doing? Some requiremements like "enter only numbers" can be done more gracefully than with the Exit event.
 

indyman

New Member
Joined
Apr 14, 2021
Messages
43
Office Version
  1. 2019
Platform
  1. Windows
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..
 

Forum statistics

Threads
1,147,517
Messages
5,741,629
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top