Excel VBA - Calling TextBox Exit Event on Userform manually?

1
votes

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?

3
vbaexceltextboxuserform
Would be very helpful to show your existing code. You don't have to put all your code in event handlers though: move the date comparison to a standalone sub and just trigger that from your event handlers. The new Sub takes care of setting the focus back to the appropriate textbox. – Tim Williams
Thanks Tim, I'll try moving it to a separate sub first. That could actually help me reduce some of my code, as I have some duplication for different text boxes currently. I didn't post the full code as it's quite lengthy (it's checking quite a few comparisons between different dates and current time) but I'll come back and edit in the relevant bits, if needed. – Goose306

3 Answers

1
votes

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.

0
votes

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.

0
votes

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()