Solved

Using the "Enter" event in a Class Module

Posted on 2001-07-11
5
279 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now