How to limit when a Class control triggers?-CodePudding

In this post, I was shown how to create a class for a textbox, so I could use the Textbox_Change event to run other code, like this example from @Storax:

Option Explicit

Public WithEvents tb As MSForms.TextBox
' just to keep track of the box in the grid
Public x As Long
Public y As Long

' Just a simple example for the change event.
' you could  use x and y to tell the different textboxes apart
Private Sub tb_Change()
    Debug.Print tb.Text, x, y
End Sub

Unfortunately, it works too well.

It fires on every keystroke in the textbox. I think I can work around that, but I'd really like it to wait until the user has finished typing, or tabbed to another control. But Textbox controls in a class module do not have Enter or Exit events.
In my main module, I have lines that change the value of the text box, but I don't always want it to trigger the event. I've tried:
```
   Application.EnableEvents = False
   Textbox1.value = "Default"
   Application.EnableEvents = True
```

...but the Change event triggers anyway.

CodePudding user response：

Use a class level variable to track where or not you want to listen for grid events.

Private Grid(1 To 10, 1 To 5) As New TextBoxListener
Private GridEventsEnabled As Boolean

Public Sub TextBoxGridChange(TextBox As MSForms.TextBox)
    If Not GridEventsEnabled Then Exit Sub
    
    Debug.Print TextBox.Value
End Sub

Private Sub UserForm_Initialize()
    
    Dim x As Long
    Dim y As Long

    For x = 1 To 10
        For y = 1 To 5
            With Grid(x, y)
                Set .TextBox = Me.Controls.Add("Forms.Textbox.1")
                Set .UserForm = Me
                With .TextBox
                    .Name = "TextBox_" & x & "_" & y
                    .Width = 50
                    .Height = 20
                    .Left = y * .Width
                    .Top = x * .Height
                    .SpecialEffect = fmSpecialEffectFlat
                    .BorderStyle = fmBorderStyleSingle
                End With
            End With
        Next y
    Next x
    
    GridEventsEnabled = True
End Sub

CodePudding user response：

Nothing can stop you to create a necessary event, as you felt is needed. In fact, replace the inconvenient one. Please, adapt your code in the next way:

Option Explicit

Public WithEvents tb As MSForms.TextBox
' just to keep track of the box in the grid
Public x As Long
Public y As Long

' Just a simple example for the Exit event.
' you could  use x and y to tell the different textboxes apart

Private Sub tb_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Debug.Print tb.Text, x, y
End Sub