excel vba userform afterupdate fires when text box cleared

Question

I have a simple userform with 5 text boxes. I can process the data using an "ADD" command button but, given that there could be 100 entries in one session it would be a bit quicker and easier if the procedure is fired with a sub [last textbox]_afterupdate().

Problem: when clearing the textboxes, the last one fires the procedure again. Here is the code:

Private Sub txtagainst2_afterupdate()


Set rTeam = [A4:A50].Find(txtTeam, lookat:=xlWhole)
Set rTeam = rTeam.Resize(1, 15)
With rTeam
    code to do stuff with the textbox values
End With
txtTeam = ""
txtFor1 = ""
txtAgainst1 = ""
txtFor2 = ""
txtAgainst2 = ""
txtTeam.SetFocus

End Sub

I've tried a few possibilities, but cannot crack it.

Any advice or solution would be truly appreciated.

Thank you, Peter

Answer 1

To prevent the code from triggering itself (or any other code), you need to temporarily disable events around the line(s) of code that trigger events to run:

Instead of just:

txtAgainst2 = ""

Use:

Application.EnableEvents = False
txtAgainst2 = ""
Application.EnableEvents = True

This will prevent the "AfterUpdate" event being triggered when the textbox is updated.

Answer 2

Michael. Thank you Your solution was one I did try before posting, but, for some reason, the step-through returns to the beginning of the procedure and runs it a second time (as before). I tried it again just now with the same result. It does not update the second time because the txtAgainst2 is empty.

But...tried an if statement at the start of the procedure (below). Had to set all but the textboxes tabstop to =false because would not setfocus to textbox1. This seems to work. Still not sure of the rationale.

private sub txtagainst2_afterupdate()

If txtAgainst2 = "" Then GoTo handler:

Set rTeam = [A4:A50].Find(txtTeam, lookat:=xlWhole)
Set rTeam = rTeam.Resize(1, 15)
With rTeam
do stuff
End With
txtTeam = ""
txtFor1 = ""
txtAgainst1 = ""
txtFor2 = ""
txtAgainst2 = ""
txtTeam.SetFocus
Exit Sub

handler: txtTeam.SetFocus Exit Sub End Sub

Thank you again for your help.