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

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