Visual Basic for Applications/Avoiding Change Event Recursion

Summary This VBA code is intended to run in Microsoft Office applications that can run macros, like Excel or Word. It provides two different examples of a text box change event, one for TextBox1 and another for TextBox2. It will be noted that on displaying the form and entering one character into TextBox1, the resulting number found there is about 290 or so; this is coded to show the number of iterations of the change event that have taken place. Doing the same in TextBox2 shows that there has been just one run of the event. The code of the TextBox2_Change event avoids multiple runs of the procedure, avoiding the possibility of false results in certain circumstances. The VBA Code Code Changes For the ThisWorkbook Module '............................................... ' Notes: Code needs a user form named UserForm1, ' with two text boxes, TextBox1 and Textbox2, '............................................... Private Sub Workbook_Open() 'Runs on opening the workbook Load UserForm1 UserForm1.Show End Sub For the UserForm1 Module Private Sub TextBox1_Change() ' This Change event runs about 294 times ' for each character entered Static nC As Long 'yield to commands-just in case DoEvents 'increment for each iteration nC = nC + 1 'this line causes this procedure to run again TextBox1.Value = nC End Sub Private Sub TextBox2_Change() ' This Change event runs only once ' for each character entered Static nC As Long Static bEnableEvents As Boolean 'yield to commands-just in case DoEvents ' increment for each iteration nC = nC + 1 ' false to start then true after that If bEnableEvents = True Then Exit Sub End If bEnableEvents = True ' this runs only once TextBox2.Value = nC ' reset flag bEnableEvents = False End Sub See Also External Links