Excel VBA UserForm TextBox Does Not Recognise Exit

Question

Hopefully this is a simple one for somebody out there. I have an Excel VBA UserForm with a number of text boxes for the entry of parameters. In addition, there is an image on the userform. As the user clicks into a textbox, the image on the UserForm is changed by triggering the _Enter() event to show an image of the parameter in question. This works fine and is not a problem. However, as the user exits the textbox, I want the image to revert back to the original image. I've tried the Events for _Exit() and _AfterUpdate() to make this work and although it works fine if I update the parameter, it doesn't work if I don't update the parameter. In essence, the image changes as I enter the text box but doesn't change as I leave unless I update the value in the text box.

Does anybody have any ideas why this is so and what I might be able to do about it?

Update (with code as requested). I've now figured out that the textbox Exit event is not being triggered because I have more than one frame on my UserForm and I am clicking on a control in a different frame. I've made the following example.

and the following code;

Option Explicit
Dim TextBox1Data As Variant, TextBox2Data As Variant
Private Sub TextBox1_AfterUpdate()
    TextBox1Data = TextBox1.Value
    Debug.Print "AfterUpdate Textbox 1"
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Debug.Print "Exit Textbox 1"
End Sub
Private Sub TextBox2_AfterUpdate()
    TextBox2Data = TextBox2.Value
    Debug.Print "AfterUpdate Textbox 2"
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Debug.Print "Exit Textbox 2"
End Sub
Private Sub UserForm_Initialize()
    ComboBox1.List = Array("Item 1", "Item 2", "Item 3", "Item 4")
    ComboBox1.Text = ComboBox1.List(1)
End Sub

If I click into TextBox1 and then click into either ComboBox1 or CheckBox1, the Exit event for TextBox1 is triggered. However, if I click into TextBox1 and then click into either CheckBox2 or CheckBox3, the event is not triggered. I assume that this is because the CheckBoxes are in a different frame. Is this the case? Is there a way of triggering an Exit event for a TextBox when moving from one frame to another?

How does the user exit the textbox? Seems like it would be either clicking on the userform or entering another textbox. Could you use the click event for the form and _Enter for the other textboxes? Treat other controls similarly. — SmileyFtW
We're going to need to see your code, because Enter and Exit events do fire exactly as expected on a brand new form with two textboxes and a button. My guess is that you've either manually-typed event signatures and they're just not invoked, or you've renamed the controls and now the handlers are just dead code. Either way, put the caret in each event handler and verify that the left-side dropdown at the top of the code pane isn't saying "(General)" (should be the name of your TextBox control). — Mathieu Guindon
AfterUpdate not firing if the value is not updated kind of makes sense, doesn't it? — Mathieu Guindon
@SmileyFtW - I tried to use the click event for the form but that doesn't work either. As I say, everything works fine if I actually change the contents of the textbox. If I simply click in and click out again, it seems to trigger the _Enter event but not the _Exit event. — JC_RMB

JC_RMB JC_RMB · Accepted Answer · 2019-11-06T09:57:20

It seems like this is a known problem - see Microsoft Q210734. Rather than use the Set Focus method suggested by MS, I've now added an Event for exiting the Frame where the textboxes are located, e.g. using my example code, I added the following;

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Debug.Print "Exiting Frame"
End Sub

What happens now is that when moving from one control to another within Frame1, the normal Exit event for the control works as expected and when moving from Frame1 to Frame2, the Exit event for the frame works.

Hope this helps someone in the same predicament.

Excel VBA UserForm TextBox Does Not Recognise Exit

1 Answers