I am using a class module to monitor events for many similar controls on a userform. I was surprised today that I could not use this method to monitor the "Enter" event for text boxes.
I have a userform with 11 rows of products and 12 columns of monthly data. The userform displays a sales plan for prior months, and allows editing of the sales plan for future months. Since each data point requires either a label or a textbox, I create them at runtime, based on the current month. There could be as many as 132 text boxes.
Whenever the user enters a textbox, I need to figure out which product line they are editing and display information about that product line elsewhere on the form.
In the past, I have set up a class module TBClass to monitor whenever any of the textboxes are changed:
Public WithEvents TBGroup as msforms.TextBox
Private Sub TBGroup_Change()
' code goes here
In the Userform code module, I have
Dim TB() as new TBClass
and then as the controls are created on the fly,
Set TB(x).TBGroup = Me.Controls("TextBox" & x)
This works fine for the change event.
When I tried to do something similar for the Enter event, I found that Enter is not a valid event for TBGroup in the class module. There are events for MouseDown, MouseUp, DblClick, but nothing for Enter.
I experimented with changing the class module definition to msforms.Control, which then offers the Enter event. However, when I try to initialize the form and assign a textbox to the class, I get a Run Time Error 459 Object or Class does not support the set of events.
Any ideas on a solution? Do I need to have 132 TextBox_Enter() macros written on the fly? Is it possible to write an _enter event macro for a control which does not yet exist?