Events In Userform Controls
The Application.EnableEvents property can be used to
suppress event procedure calls for Excel objects, such the Change event of a
Worksheet. This is used to prevent an infinite loop. For example, if
you have the following code in the Sheet1 module, to put the date and time
in the cell next to a cell changed by the user:
Private Sub Worksheet_Change(ByVal Target As Range) Target.Offset(0, 1).Value = Now End Subthis will cause an infinite loop (actually VBA will detect this and shut it down after about 250 iterations of the loop) because the code changes a cell, which causes the Change event to run, which changes a cell, which causes the Change event to run, which changes a cell, and so on and on. The normal remedy for this is to use Application.EnableEvents = False to prevent the events from being triggered.
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ErrH: Target.Offset(0, 1).Value = Now ErrH: Application.EnableEvents = True End Sub
This will prevent the Change event from calling itself. The On Error handling is structured so that if an error does occur, EnableEvents will still be returned to True.
However, EnableEvents does not affect the event behavior of controls on a userform. For example, the Change event of a TextBox on a UserForm will execute regardless of the setting of Application.EnableEvents. If you need to suppress events within a user form, you have two alternatives. The first is to create a module-level Boolean variable (a module-level variable is Dim'd above and before any procedure in the module, and its value is accessible to read or change by any procedure within that module:
Private FormEnableEvents As Boolean
Then your event code would test the value of this variable, and if it is True, exit immediately. For example,
Private Sub TextBox2_Change() If FormEnableEvents = False Then Exit Sub End If '''''''''''''''' ' your code here '''''''''''''''' FormEnableEvents = True End Sub
However, this approach has two significant flaws. First, it is global to all controls on the form. It may be the case that you do in fact what the Change (or other events) of some objects to run when their value is changed. Using a module level variable doesn't allow you to discriminate between when code event code should or should not run for a specific control. Second, it may not be possible or at least not easy to determine exactly where and in what procedure the FormEnableEvents variable should be set to True or False.
A better approach is to handle the event procedure within itself, without using a module-wide variable. For example, suppose you want to prevent the user for entering a tab character into a TextBox. (This is just an example. A better approach to this particular problem is described on the Restricting Entry In A Text Box page.) In the Change event of the TextBox, use code like the following:
Private Sub TextBox1_Change() Static AutoAction As Boolean Dim TabPos As String If AutoAction = True Then Exit Sub End If AutoAction = True With Me.TextBox1 ' Your code here. ' For example, .Text = Replace(.Text, Chr(9), "") End With AutoAction = False End Sub
This code is an entirely self-contained procedure that doesn't rely on other, external variables. It works as follows. The AutoAction variable is declared as Static, so it will retain its value even after the sub exits. The error handling is set up so that the AutoAction variable is properly reset if an error occurs.
The first time the text box is changed, AutoAction is not True, so we don't Exit Sub. We then set AutoAction to True and then make a change to the text box, in this case replacing the tab character, Chr(9), with an empty string. This will cause the Change event to run again. However, this time AutoAction is True, so we immediately exit the sub. Execution will resume at the End With statement (or whatever statement follows the line of code that changed the text box), and finally we reset AutoAction back to False so the next time the user changes the text box, the change will go through. If this seems complicated, just set a breakpoint on theIf AutoAction = True Then line (select the line and press F9 to turn a breakpoint on or off) and step through the code line by line. You'll find that it is really quite simple. All is really does is kick us out of the Change event if the event was trigger as a result of code, rather than the user changing the textbox's value.