Textbox loop check to validate entry

[D_N_L]

Sorry - rubbish title.

I'm looking for some advice. I have 50 Textboxes on a userform and I wish to prevent a user from entering info in each one that begins with an 'I'. My code works fine but naturally I don't wish to have it repeated 50 times. How would I incorporate it into a loop and what would I call the Sub?

Please Login or Register  to view this content.

[nathansav]

You can do whats called event sinking, how are you with VBA?

[D_N_L]

Hi Nathan,

Out of 10? Probably a 5.

Six on a good day!

[nathansav]

Ok, i'll tell you what to do and send a sample.

You need the following in your userform

Please Login or Register  to view this content.

Then you need a class, called clsCustomTextBox, which has the code

Please Login or Register  to view this content.

What this does is pass every textbox on the form initialisation and diverts it to be our customtextbox, which is inherited from MsForms.Textbox, we just mess about with the events.

HTH

[D_N_L]

I see, thank you very much.

I have a few questions if I may?

• The class part. I have inserted a class module – is this what you mean?
• Will your code apply to all textboxes? I have other textboxes where it’ll be ok for the user to start their TB entry with ‘I’. Although the ones I want it to apply to all share a similar naming pattern – could I use that? So instead of:

Please Login or Register  to view this content.

Could I use:

Please Login or Register  to view this content.

• Lastly I receive the following error upon compile (user type not defined):

Please Login or Register  to view this content.

[nathansav]

1 Yes it does, you will need to put after the if Typename line,

Please Login or Register  to view this content.

this will then omit the sinking for control1 and control2. Typename is the type name not the name.

2 Have you named your class clsCustomTextbox?

[D_N_L]

That's great. Thanks a lot Nathan!! : )

[mikerickson]

You could put this event code in the class module

Please Login or Register  to view this content.