Solved

Using the "Enter" event in a Class Module

Posted on 2001-07-11
5
298 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

749 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