Useing Class structures and Events

[Dan Gardner]

I have created a class of textbox objects on a form with the following code;
Dim Listings() As New Class1
Dim listcount As Integer
Dim ctl As Control
listcount = 0

For Each ctl In Tag_Priority.Controls
If TypeName(ctl) = "TextBox" And ctl.Visible = True Then
listcount = listcount + 1
ReDim Preserve Listings(1 To listcount)
Set Listings(listcount).listgroup = ctl
End If
Next ctl

Class Module -
Public WithEvents listgroup As MSForms.TextBox
Private Sub listgroup_Change()

Bla Bla Bla

End sub

The problem is that the Change() event triggers each time a single digit is
modified in the included textbox’s. It works great for my Bla Bla Bla code
when my textbox.text inputs are single digits 1,2,3, etc. but is a disaster
when I want to enter double digit numbers like 11,12,13, etc., each number is
its own textbox. I was hoping there is an event trigger or something I could
use that would trigger when I select another object either in that class or
on that form.

The form presents a list with associated priorities as texboxes, the idea is
that the user can change the textbox value which will then reprioritize the
list based on the new individual textbox inputs.

I’m using Office 2000, I would apriciate any help that could be offered, I’m
twisting my brain up in knots with logic statements. Thanks,

[Tushar Mehta]

Except in extremely limited cases, I find the change event (or the exit=20
event if you are so tempted) of textboxes to be practically useless for=20
any meaningful validation. The most reliable approach is to wait until=20
the user clicks the OK button and then validate the userform contents.

--=20
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,=20
[email protected] says...
> I have created a class of textbox objects on a form with the following co=
de;
> Dim Listings() As New Class1
> Dim listcount As Integer
> Dim ctl As Control
> listcount =3D 0
> =20
> For Each ctl In Tag_Priority.Controls
> If TypeName(ctl) =3D "TextBox" And ctl.Visible =3D True Then
> listcount =3D listcount + 1
> ReDim Preserve Listings(1 To listcount)
> Set Listings(listcount).listgroup =3D ctl
> End If
> Next ctl
>=20
> Class Module -
> Public WithEvents listgroup As MSForms.TextBox
> Private Sub listgroup_Change()
>=20
> Bla Bla Bla
>=20
> End sub
>=20
> The problem is that the Change() event triggers each time a single digit =
is=20
> modified in the included textbox=E2=A4=3D3Fs. It works great for my Bla B=
la Bla code=20
> when my textbox.text inputs are single digits 1,2,3, etc. but is a disast=
er=20
> when I want to enter double digit numbers like 11,12,13, etc., each numbe=
r is=20
> its own textbox. I was hoping there is an event trigger or something I c=
ould=20
> use that would trigger when I select another object either in that class =
or=20
> on that form.=20
>=20
> The form presents a list with associated priorities as texboxes, the idea=
is=20
> that the user can change the textbox value which will then reprioritize t=
he=20
> list based on the new individual textbox inputs.=20
>=20
> I=E2=A4=3D3Fm using Office 2000, I would apriciate any help that could be=
offered, I=E2=A4=3D3Fm=20
> twisting my brain up in knots with logic statements. Thanks,
>=20
>=20

[Stephen Bullen]

Hi Dan,

> The form presents a list with associated priorities as texboxes, the idea is
> that the user can change the textbox value which will then reprioritize the
> list based on the new individual textbox inputs.

Two potential alternatives come to mind:

1. Instead of reacting to the change event, have a separate 'Resort list'
button to do the reprioritisation.

2. Add a timer to the form (e.g. the IETimer.ocx available from the MS web
site), set to fire after (say) three seconds. Each time a text box is changed,
disable and reenable the timer. In the timer's event, reprioritize the list
then disable the timer again. The list should then update 'automagically' three
seconds after you stop typing.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk