Excel VBA - Calling TextBox Exit Event on Userform manually?

I'm building a userform where it has two text boxes to enter dates. Once the date is entered, I'm validating them when the Exit event fires. If the validation turns up that the user data isn't what is needed, the user is notified, the text box is cleared, and the focus is returned back to the textbox.

The issue comes if the user uses the mouse to select outside of the box, rather than Tab. If Tab is used, it fires perfectly and as expected, and the field is cleared and the focus is returned. If the mouse is used, it doesn't fire. According to this article, this is expected behavior (It's for Access, but I didn't see the similar relevant MSDN article for Excel.)

So instead I tried the AfterUpdate event. However, SetFocus doesn't work within an AfterUpdate event, I'm assuming because of the chain of events as outlined in the response to this question. Thus, I don't have a way to return the focus back to the textbox after it has fired. That thread had a suggestion as an alternate answer to SetFocus to another control and come back as a workaround, but that doesn't work for me, so I assume that may be an Access-specific workaround.

My last option I've considered is having the AfterUpdate event just call the Exit event, however the Exit event has a required argument (ByVal Cancel As MSForms.ReturnBoolean), which is how you cancel out of the exit and return the user to the textbox. As such, there isn't a value that you can pass to it that doesn't throw an error that I can find (the closest I found was passing Nothing but it failed out when trying to set it to True later to cancel the exit.)

Is there a way to achieve what I'm looking for here, or should I just stick to the AfterUpdate and ignore the SetFocus I'm trying to achieve?

Answers


Exit event works on all the mouse clicks which fire up Enter for another Control on the Form. But When you click, directly on the form instead of any other control, nothing happens.

Here, use the ActiveControl property to determine about the last control you were in, before exiting and moving to user form.

Sample Code, rework it according to your requirement.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call doValidation(Me.TextBox1.Text)  '/ Validation Routine when user leaves TextBox
End Sub

Private Sub UserForm_Click()
    '/ If user clicked on the user form instead of ay other control
    If Me.ActiveControl.Name = Me.TextBox1.Name Then
        Call doValidation(Me.TextBox1.Text)  '/ Validation Routine when user leaves TextBox
    End If
End Sub

Private Sub doValidation(strText)
        MsgBox strText
End Sub

Advise: For Date inputs, use DateTimePicker instead of TextBox, will save you from alot of trouble in future.


I know that this was answered a few months back but giving an alternative solution. For any one who finds this question.

For validation of Excel Textbox data use the BeforeUpdate Event, it fires before the AfterUpdate Event and has the ability to prevent losing Focus on the control.

Rework the sample code to your requirements

Remember Cancel = True stops the control update to the control and it remains in focus.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = doValidation(Textbox1.Text) 'Validation Route
End Sub

Private Function doValidation(strText) as Boolean
    'Do Validation
    if Valid then
        doValidation = False
    Else
        msgBox "Not Valid"
        doValidation = True
    End if
End Sub

In my opinion this is the easiest way to validate an input on an Excel Userform Textbox

I can't right now find the correct MSDN article at this time, all Google wants to return is Access Results.


In VBA you can call any defined sub or function with the word Call Subname: e.g. Call Textbox1_exit(params)

However from the somewhat confusing description I believe your problem is that you limit yourself to just a few event functions. I would suggest exploring all event functions and see which one is a good fit for your event fire. Here is a list of events and their sequences in Access VBA: https://msdn.microsoft.com/en-us/library/office/jj249049.aspx

and the order of form events:

https://support.office.com/en-us/article/Order-of-events-for-database-objects-e76fbbfe-6180-4a52-8787-ce86553682f9

I think for your application from the description you gave the lost_focus or got_focus for certain components might be useful. Furthermore, you can manually set the focus to almost any component it is a built in method: compName.SetFocus()


Need Your Help

Weak method argument semantics

swift arguments automatic-ref-counting weak-references

Is there any way to specify that a particular method argument has weak semantics?