Search code examples
excelvbauserform

Excel VBA UserForm TextBox Does Not Recognise Exit

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. Simple UserForm with Two Frames

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?

Solution

  • 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.