design one event handler for multiple textboxes

[clara]

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

 

[Vergel Adriano]

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.

 

[clara]

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

 

[Vergel Adriano]

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

 

[Doug Glancy]

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

 

[Vergel Adriano]

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