Multiple Control Event Classes

[MartinS]

Hi all
I have set up a form with sixty textbox controls on it to allow the user
to enter some data. After starting to write Enter, KeyPress and
AfterUpdate event procedures for each control, I realised that there
must be an easier way, so did a bit of hunting and found the class event
approach. Deleted all my procedures and set it all up again using the
class approach. Now, the problem I'm having is that I have more than
one event for each control, i.e. AfterUpdate, KeyPress and Enter. The
keypress event seems to fire perfectly, but the Enter and AfterUpdate
events don't. I have set up a different class for each data entry type
(I'm entering dates and numbers) and when populating the control arrays
at form initialisation, I determine what to do with each textbox control
by specific text in it's name.
Are there issues with using multiple class events for the same control?
Is there a way to get this to work?

Regards, and thanks in advance

Martin

This is what I have done (if it's of any use):

Private TBDATES() As New TBDateClass
Private TBNUMS() As New TBNumberClass

Private Sub UserForm_Initialize()
Dim TBD_COUNT As Integer, TBN_COUNT As Integer
Dim CTL As Control
TBD_COUNT = 0
TBN_COUNT = 0
For Each CTL In Me.Controls
With CTL
If TypeName(CTL) = "TextBox" And .Parent.Name = "Frame1"
Then
If InStr(.Name, "DATE") > 0 Then
TBD_COUNT = TBD_COUNT + 1
ReDim Preserve TBDATES(1 To TBD_COUNT)
Set TBDATES(TBD_COUNT).TBDGroup = CTL
Else
TBN_COUNT = TBN_COUNT + 1
ReDim Preserve TBNUMS(1 To TBN_COUNT)
Set TBNUMS(TBN_COUNT).TBNGroup = CTL
End If
End If
End With
Next CTL
End Sub

Then I have set up two class modules:

TBDateClass

code:
Public WithEvents TBDGroup As MSForms.TextBox

Private Sub TBDGroup_AfterUpdate()
With TBDGroup
'...my code
End With
End Sub

Private Sub TBDGroup_Enter()
With TBDGroup
'...my code
End With
End Sub

Private Sub TBDGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'...my code
End Sub

TBNumberClass

code:
Public WithEvents TBNGroup As MSForms.TextBox

Private Sub TBNGroup_AfterUpdate()
With TBNGroup
'...my code
End With
End Sub

Private Sub TBNGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'...my code
End Sub

 

[Jan Karel Pieterse]

Hi MartinS,

Now, the problem I'm having is that I have more than
one event for each control, i.e. AfterUpdate, KeyPress and Enter. The
keypress event seems to fire perfectly, but the Enter and AfterUpdate
events don't. I have set up a different class for each data entry type
(I'm entering dates and numbers) and when populating the control arrays
at form initialisation, I determine what to do with each textbox control
by specific text in it's name.

When using a class module to hook up events for multiple controls, not all
events you are used to seeing in the code module behind the form are
available.

The textbox control e.g. has no AfterUpdate event from a class module.
Find out which are present by using the dropdowns at the top of the class
module's window. You'll see a number of them are missing.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[MartinS]

Hi
Not what i wanted to hear, but thanks
Martin

 

[Jamie Collins]

I have set up a form with sixty textbox controls on it to allow the user
to enter some data. After starting to write Enter, KeyPress and
AfterUpdate event procedures for each control

Many of us have trod this path and come to the same conclusion: it's a
lot easier to have a button captioned Apply / Done / OK / etc to
trigger form-level validation of controls. I think did once get the
Enter / Exit / etc events to work correctly, perhaps not in a separate
class module, but it was not a happy experience.

If you need to do something dynamically, e.g. change the data in one
control based on the data entered in another, use a control that
chooses a definitive value with a Click or similar event e.g. a
dropdown, list, date-picker/calendar, etc.

When I need to use a free-type control such as a TextBox, I often lock
it and provide a Change button beside it: clicking the button brings up
an inputbox-style dialog for which they must click Apply / Done / OK
(or Cancel) to trigger control-level validation for the value entered.
This is a little extreme (and may not be practical for 60 controls) but
at least I know when the user has finished typing the value. [Aside:
have you asked you users whether 60 textboxes on a single form makes
for a happy user experience?]

Jamie.

--

 

[MartinS]

Hi Jamie
Thanks for your comments. I have managed to whittle the form down from
180 to 60 controls, and one 'column' of data can be picked from a
calendar control, and another, if the relevant checkbox is applied
earlier, is pre-populated by existing data, so it's not all bad. I
could whittle it down to 30, but the overall plan is to get the user to
enter 3 years worth of data, month by month, so by having one year's
worth on a form, the user can 'add' once populated and keep going until
the required amount is reached.
I do see you point, but with the layout of the form, it's just not
practical.
I've ended up with an AfterUpdate event for each control and an Enter
event for 12 of the controls, and the keyPress event is still inside the
class event handler, so it's not that bad.
Anyway, again, thanks for your comments - much appreciated.
Regards
Martin