Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using the "Enter" event in a Class Module

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

856 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