• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Using the "Enter" event in a Class Module

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.

Background:
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.

Problem:
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.

Failed Approach:
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
    End Sub

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?

TIA,

Bill
0
jelen
Asked:
jelen
  • 2
  • 2
1 Solution
 
sebastienmCommented:
Hi Bill,
The Enter event is not accessible from a Public WithEvents clause of a Class module.
However, if you just need to access the 'user press the ENTER key' and not exit your control by clicking somewhere else on the form, you can use the KeyUp event:

Private Sub TextBox1_KeyUp(
   ByVal KeyCode As MSForms.ReturnInteger,
   ByVal Shift As Integer)

   If KeyCode = 13 Then  '=ENTER
     'Code Here
   End If

End Sub

Regards,
Sebastien
0
 
jelenAuthor Commented:
I am trying to figure out as soon as they enter a textbox, either by mouse click, or by using the tab key.  

The TextBox1_Enter() procedure seems to do this,  I was hoping to avoid having 132 of these procedures, but apparently that won't be possible.

Bill
0
 
sebastienmCommented:
132 textboxes requires way too much resources.
My suggestion:

Userform
---------

Sales Plan Summary
------------------
Grid to display 1 Cell per (Product,Month)
(make it non editable)
Very easy to capture the selected cell and you just
manage 1 object.

Detail
------
Elsewhere in form, detail for the selected cell
In this part, you can easily have the user edit
whatever they want.

--------------------------------
If it is in Excel, you can bound the grid or details directly to you sheet data. So where the user changes the
grid selection or edit details, it is directly updated
in the sheet data and vise versa.
Same with if you tight your form to a dB.

Regards,
Sebastien
0
 
calacucciaCommented:
Bill, Sebastien,

I've been trying to make the 'ClassEvents' method for multiple controls work in Excel 97, at no avail.

Is this Office 2000 only?
0
 
calacucciaCommented:
Oops,

It does work, I've forgotten to declare the new class objects in the userform globally.

Thanks for this great example, Bill, I'll sure be able to use this ;-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now