Multiple textbox validation & tab order

**StephenR** · Feb 24th 2006

Hello. I have a form with around 50 textboxes.

Firstly, is there a quick way to set the tab order so that it is automatically in sequence, rather than having to go through each one manually?

Secondly, is there a quick way to validate all 50 in one go? I want to ensure that only numbers 0-9 are entered in each textbox. Previously, I've used a validation script on a single textbox using KeyPress & Ascii codes 48-57, but I don't know if this can be adapted for multiple textboxes.

Thanks in anticipation.

Stephen

**norie** · Feb 24th 2006

Re: Multiple textbox validation & tab order

Stephen

For the first question try right clicking the userform and selecting Tab Order.

For the second perhaps the attached workbook can help.

**StephenR** · Feb 24th 2006

Re: Multiple textbox validation & tab order

Thanks norie. Tab Order is fine, should have spotted that. Re 2nd point, I've looked at your example which doesn't work on my form but I'm looking into that. I owe you two!

**norie** · Feb 24th 2006

Re: Multiple textbox validation & tab order

The attachment is really only an example.

To get it to work with what you have several changes would be needed.

**StephenR** · Mar 2nd 2006

Re: Multiple textbox validation & tab order

norie,
Apologies for reviving this long after it's probably slipped from your mind but I am having trouble understanding your attachment. I've just tried to get it to work on my form, exactly as it stands, but nothing happens. There's no error, but it doesn't stop me from entering non-numeric data. Any idea why this might be?
Help from anyone at all welcome of course. Thanks. The code is as follows:

Quote

Dim TextBoxes() As New CTextboxes

Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long
I = 1

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ReDim Preserve TextBoxes(1 To I)
Set TextBoxes(I).TextGroup = ctl
I = I + 1
End If
Next ctl
End Sub

Display More

Class Module

Quote

Option Explicit

Public WithEvents TextGroup As MSForms.TextBox

Private Sub TextGroup_Change()
If Len(TextGroup.Value) > 0 And Not IsNumeric(TextGroup.Value) Then
MsgBox "Only numeric data, thank you"
TextGroup.Value = Left(TextGroup.Value, Len(TextGroup.Value) - 1)
End If
End Sub

Display More

**norie** · Mar 2nd 2006

Re: Multiple textbox validation & tab order

Can you attach a sample workbook?

**StephenR** · Mar 2nd 2006

Re: Multiple textbox validation & tab order

Here it is. I've cut a lot out to get it down to manageable size but I don't think that should affect anything.

**norie** · Mar 2nd 2006

Re: Multiple textbox validation & tab order

Stephen

In my example you'll notice that this isn't in a sub.

Code

Dim TextBoxes() As New CTextboxes

Useform module.

Code

Option Explicit
Dim TextBoxes() As New CTextboxes


Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control, I As Long
    
    TextBox1.SetFocus
    
    I = 1
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            ReDim Preserve TextBoxes(1 To I)
            Set TextBoxes(I).TextGroup = ctl
            I = I + 1
        End If
    Next ctl
    
End Sub


Private Sub cmdOK_Click()
    Unload Me
End Sub


Private Sub cmdCancel_Click()
    Unload Me
End Sub

Display More

**StephenR** · Mar 2nd 2006

Re: Multiple textbox validation & tab order

Thanks norie. Believe it or not I spotted that and changed it and I could have sworn I got an error message but I must have done something wrong. Thanks a lot. Btw what's the significance of placing it outside a sub?

**norie** · Mar 2nd 2006

Re: Multiple textbox validation & tab order

To be honest I don't rightly know.:)

It's probably something to do with scope, placing it in the sub means it's only available within that sub.

Multiple textbox validation & tab order

Participate now!

Share