Hi all,

In a form, there are 30 textboxes who all need the limitation of 30 chars in
length. I know code can be put in the change event, but if I do it in that
way, I will use 30 event handlers even I can abstract the core logic into a
sub.Is there a way to use one handler for all text box controls

Clara
--
thank you so much for your help

You cannot have one event handler for multiple controls.. at least not in
Excel 2003, not sure about 2007. The most you can do is put all the logic in
one subroutine and call that in the individual event handlers. However, for
the example that you gave, you can just set the MaxLength property of the
textboxes to 30.


--

Hope that helps.

Vergel Adriano


"clara" wrote:

> Hi all,
>
> In a form, there are 30 textboxes who all need the limitation of 30 chars in
> length. I know code can be put in the change event, but if I do it in that
> way, I will use 30 event handlers even I can abstract the core logic into a
> sub.Is there a way to use one handler for all text box controls
>
> Clara
> --
> thank you so much for your help

Hi Vergel,

Thanks a lot for your help! Even the max length limitation can be done
through setting the corresponding property, but for 30 textboxes it is still
tedious, can you figure out a way to do it just in one time?

Clara
--
thank you so much for your help


"Vergel Adriano" wrote:

> You cannot have one event handler for multiple controls.. at least not in
> Excel 2003, not sure about 2007. The most you can do is put all the logic in
> one subroutine and call that in the individual event handlers. However, for
> the example that you gave, you can just set the MaxLength property of the
> textboxes to 30.
>
>
> --
>
> Hope that helps.
>
> Vergel Adriano
>
>
> "clara" wrote:
>
> > Hi all,
> >
> > In a form, there are 30 textboxes who all need the limitation of 30 chars in
> > length. I know code can be put in the change event, but if I do it in that
> > way, I will use 30 event handlers even I can abstract the core logic into a
> > sub.Is there a way to use one handler for all text box controls
> >
> > Clara
> > --
> > thank you so much for your help

Hi Clara,

Yes, you can set the MaxLength property by code. Put this in the
UserForm_Initialize sub

Dim c As Control
For Each c In Me.Controls
If TypeOf c Is MSForms.TextBox Then
c.MaxLength = 30
End If
Next c


--

Hope that helps.

Vergel Adriano


"clara" wrote:

> Hi Vergel,
>
> Thanks a lot for your help! Even the max length limitation can be done
> through setting the corresponding property, but for 30 textboxes it is still
> tedious, can you figure out a way to do it just in one time?
>
> Clara
> --
> thank you so much for your help
>
>
> "Vergel Adriano" wrote:
>
> > You cannot have one event handler for multiple controls.. at least not in
> > Excel 2003, not sure about 2007. The most you can do is put all the logic in
> > one subroutine and call that in the individual event handlers. However, for
> > the example that you gave, you can just set the MaxLength property of the
> > textboxes to 30.
> >
> >
> > --
> >
> > Hope that helps.
> >
> > Vergel Adriano
> >
> >
> > "clara" wrote:
> >
> > > Hi all,
> > >
> > > In a form, there are 30 textboxes who all need the limitation of 30 chars in
> > > length. I know code can be put in the change event, but if I do it in that
> > > way, I will use 30 event handlers even I can abstract the core logic into a
> > > sub.Is there a way to use one handler for all text box controls
> > >
> > > Clara
> > > --
> > > thank you so much for your help

Clara,

You can do it by defining a textbox class. This example is for buttons, but
can be modified:

http://www.j-walk.com/ss/excel/tips/tip44.htm

hth,

Doug

"clara" <(E-Mail Removed)> wrote in message
news:CDDD41DD-B5FB-47A9-8104-(E-Mail Removed)...
> Hi all,
>
> In a form, there are 30 textboxes who all need the limitation of 30 chars
> in
> length. I know code can be put in the change event, but if I do it in that
> way, I will use 30 event handlers even I can abstract the core logic into
> a
> sub.Is there a way to use one handler for all text box controls
>
> Clara
> --
> thank you so much for your help

And also, in the Form designer, you can select all textboxes then set the
MaxLength property to 30 just one time.



--

Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

> Hi Clara,
>
> Yes, you can set the MaxLength property by code. Put this in the
> UserForm_Initialize sub
>
> Dim c As Control
> For Each c In Me.Controls
> If TypeOf c Is MSForms.TextBox Then
> c.MaxLength = 30
> End If
> Next c
>
>
> --
>
> Hope that helps.
>
> Vergel Adriano
>
>
> "clara" wrote:
>
> > Hi Vergel,
> >
> > Thanks a lot for your help! Even the max length limitation can be done
> > through setting the corresponding property, but for 30 textboxes it is still
> > tedious, can you figure out a way to do it just in one time?
> >
> > Clara
> > --
> > thank you so much for your help
> >
> >
> > "Vergel Adriano" wrote:
> >
> > > You cannot have one event handler for multiple controls.. at least not in
> > > Excel 2003, not sure about 2007. The most you can do is put all the logic in
> > > one subroutine and call that in the individual event handlers. However, for
> > > the example that you gave, you can just set the MaxLength property of the
> > > textboxes to 30.
> > >
> > >
> > > --
> > >
> > > Hope that helps.
> > >
> > > Vergel Adriano
> > >
> > >
> > > "clara" wrote:
> > >
> > > > Hi all,
> > > >
> > > > In a form, there are 30 textboxes who all need the limitation of 30 chars in
> > > > length. I know code can be put in the change event, but if I do it in that
> > > > way, I will use 30 event handlers even I can abstract the core logic into a
> > > > sub.Is there a way to use one handler for all text box controls
> > > >
> > > > Clara
> > > > --
> > > > thank you so much for your help