Solved

Using the "Enter" event in a Class Module

Posted on 2001-07-11
5
286 Views
Last Modified: 2010-08-05
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
Comment
Question by:jelen
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
sebastienm earned 100 total points
ID: 6274527
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
 
LVL 1

Author Comment

by:jelen
ID: 6274557
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
 
LVL 16

Expert Comment

by:sebastienm
ID: 6274647
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 6379269
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 6379307
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question