Dynamic Event handler wanted.

[RadarEye]

Hi you all,

I have created a tool in Excel 2003 which created dynamically a user
form. On this form there are several textboxes on which I would like
validation. The value entered must be a valid date. I am not allowed
to use any add-in like MSCAL.OCX. I would like to use the exit-event.
How can I dynamically add this event to the textboxes I have created
with:

With Me.Controls.Add("Forms.TextBox.1", "txtName" & strName, True)
.Top = 200
.Left = 100
.Height = 16
.Width = 50
.ControlSource = "Answers!B1"
.Font.Size = 8
End With

Ii have tried it with a class:
' class CtxtEvents
Option Explicit

Public WithEvents oTxt As MSForms.TextBox

Private Sub oTxt_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If oTxt.Text <> vbNullString Then
If Not IsDate(oTxt.Text) Then
MsgBox "Geen datum"
Cancel = True
End If
End If
End Sub

Used as:
Dim oTxtEvnts As CtxtEvents
Set oTxtEvnts = New CtxtEvents
Set oTxtEvnts.oTxt = Me.Controls.Add("Forms.Textbox.1", "MijnText",
True)
With oTxtEvnts.oTxt
.Left = 200
.Top = 150
.Height = 16
.Width = 200
.Text = Format(Date, "dd-mm-yyyy")
End With

Is this possible?

 

[Chip Pearson]

The short answer is that you can't do with a textbox what you are
trying to do. You can do it with other events of a text box, and with
events of other controls, byt not with either the Exit or Enter event
of a textbox.

The MSForms TextBox control is something of an odd duck. Unlike most
other controls, some of the TextBox's events are handled by the
container form and aren't exposed to a class that instantiates the
text box. Both the Enter and Exit events are not exposed when an
object module instantiates the textbox control class. There's really
no way to get the Exit and Enter events at runtime. Your code will
work for textbox events that are not handled by the form, such as
Change and KeyDown as well as for other form contols. Dynamically
building the code into the userform's module won't work either.

Probably the best way to do this is to create any textboxes that might
be needed during the design phase and then making visible and
positioning the appropriate box at run time.

I don't know why the TextBox contols works (or fails to work) as it
does. I suppose there is a reason, but I don't know what it is.

Cordially,
Chip Pearson
Microsoft MVP
Excel 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]