VBA Excel Exiting after update event fires enter event

Question

Let's say I had a combobox1, 2, 3 and a textbox1 on a userform. After update event was associated to combobox1. When Tab key was pressed after update event was fired (filling comboboxes 2 and 3). In the code of this event, just before exit sub there was textbox1.setfocus to skip entering other coboboxes in the tab order (combobox 1,2,3, textbox1). It worked just fine.

When I added another combobox that is now combobox2, the tab order was changed to 1,2,3,4. After update is still associated to combobox1 and textbox1.setfocus is last line before exit sub. Unfortunately when exit sub line for after update is executed, it fires combobox3 enter event and moves focus to combobox3. It's more incomprehensible because tab order is combobox1,2,3,4 so it skips the tab order as well.

When I debuged code, the focus to textbox1 was set as it should but still just when exit sub is executed, code line is moved to combobox3 enter event... Any tips appreciated.

Application.Enableevents=False - prevent the other events from firing. Before Exit Sub or End Sub turn the events back on. — vacip
@vacip This might help but still - how come it started to behave lik this ? — kkris77
@vacip One more question to you your solution - when I should set enable event to false and when to true ? I need to execute after update event, but enter event fires just when 'exit sub' is executed for after aupdate event and you suggested to turn enable events on just before 'exit sub'. — kkris77
Hmm, I'm not sure I totally understand. Why don't you just disable tab stops if you want to force focus to stay in the same control after tab? Maybe EnableEvents is not good for you; you can try setting and checking global flags in the different events to decide if they should fire or not... — vacip
Well this is how it works: if the data entered in combobox1 exists in the database, combobox 2,3,4 are filled automatically so I want the focus skip entering this comboboxes (according to tab order) and just move to textbox1. But if the data entered in comobox1 doesn't exists in the database, user has to input data for combobox 2,3,4 before entering textbox1 and in this case I want to use benefits of tab order. — kkris77

user3598756 user3598756 · Accepted Answer · 2017-01-02T09:21:56

in your userform code pane act as follows:

a userform scoped Boolean variable

hence, place this before any Sub/Function code
```
Dim SetTextBox1Focus As Boolean
```

in your ComboBox1_AfterUpdate() event handler place:

Private Sub ComboBox1_AfterUpdate()
    If (condition that checks if the data entered in combobox1 exists in the database) is True Then

        ...
        your code to fill comboboxes 2, 3 ad 4
        ...

        Me.TextBox1.SetFocus
        SetTextBox1Focus = True '<--| "flag" TextBox1 to receive the focus
    End If
End Sub

add an Enter event handler for all userform controls whose tab index is in between ComboBox1 and TexBox1

for instance, assuming those are ComboBox2, ComboBox3 and ComboBox4

Private Sub ComboBox2_Enter()
    CheckSetTextBox1Focus
End Sub


Private Sub ComboBox3_Enter()
    CheckSetTextBox1Focus
End Sub


Private Sub ComboBox4_Enter()
    CheckSetTextBox1Focus
End Sub

add the following CheckSetTextBox1Focus() Sub

Sub CheckSetTextBox1Focus()
    If SetTextBox1Focus Then
        Me.TextBox1.SetFocus
        SetTextBox1Focus = False
    End If
End Sub

VBA Excel Exiting after update event fires enter event

1 Answers