Validate multiple text boxes to accept only numeric values

[KTSARA]

Hello experts,
I am quite novice to VBA and any help from you would be highly appreciated.
I have 44 text boxes in my user form which I need only to accept numeric values. Every text box name has a "Quantity" part (ex. InhouseQuantityTextBox, PurchasedMaterialQuantity,etc.), but, they are not numbered from 1-44. Other text boxes in the user form should behave normally. I have two userforms of this kind and I want to apply the same condition to the other user form as well. Is there a way to make all these quantity text boxes accept only numeric values. If you could explain an answer in the simplest way, I would be much grateful as I couldn't modify most of the answers published in the internet because I couldn't understand them.

This is the code I used to make one text box accept only numeric values, but repeating it for 44 times is a headache.

Private Sub MaterialQuantityTextBox_Change()

    If TypeName(Me.ActiveControl) = "TextBox" Then
        With Me.ActiveControl
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                MsgBox "Sorry, only numbers allowed"
                .Value = vbNullString
            End If
        End With
    End If

Thanks a lot in advance

End Sub

 

[dmt32]

Hi,

With so many textboxes, the way to resolve is to create a class & apply a single code to limit users entering (not coy & paste) values other than numeric



From VBA editor,

1 - Insert a Class Module Insert > Class Module

2 – Name the Module TextBoxClass

3 – Place Following code in the Class Module

Public WithEvents TextBoxClass As MSForms.TextBox

Private Sub TextBoxClass_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
'valid entries [0-9]
    Case 48 To 57

    Case Else
'cancel
        KeyAscii = 0
    End Select
End Sub

4 Place following code in your userforms code page

Dim TextBox() As New TextBoxClass

Private Sub UserForm_Initialize()
    Dim Count As Integer
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            If Ctrl.Name Like "*Quantity*" Then
            Count = Count + 1
            ReDim Preserve TextBox(1 To Count)
                Set TextBox(Count).TextBoxClass = Ctrl
            End If
        End If
    Next
End Sub

Solution should only include those textboxes with "Quantity" in the name & prevent users typing anything other than numeric values (0-9)

Hope this will do what you want

Dave

 

[KTSARA]

He

Hi,

With so many textboxes, the way to resolve is to create a class & apply a single code to limit users entering (not coy & paste) values other than numeric



From VBA editor,

1 - Insert a Class Module Insert > Class Module

2 – Name the Module TextBoxClass

3 – Place Following code in the Class Module

Public WithEvents TextBoxClass As MSForms.TextBox

Private Sub TextBoxClass_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
'valid entries [0-9]
    Case 48 To 57

    Case Else
'cancel
        KeyAscii = 0
    End Select
End Sub

4 Place following code in your userforms code page

Dim TextBox() As New TextBoxClass

Private Sub UserForm_Initialize()
    Dim Count As Integer
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            If Ctrl.Name Like "*Quantity*" Then
            Count = Count + 1
            ReDim Preserve TextBox(1 To Count)
                Set TextBox(Count).TextBoxClass = Ctrl
            End If
        End If
    Next
End Sub

Solution should only include those textboxes with "Quantity" in the name & prevent users typing anything other than numeric values (0-9)

Hope this will do what you want

Dave

Hello Dave,
Thank you for taking your valuable time to answer my question. I tried the code and I get an error message saying, "Compile error: Variable not defined" and it highlight the below line:

ReDim Preserve TextBox(1 To Count)

Could you please help me in correcting this error.
Thanks a lot in advance

 

[dmt32]

He

Hello Dave,
Thank you for taking your valuable time to answer my question. I tried the code and I get an error message saying, "Compile error: Variable not defined" and it highlight the below line:

ReDim Preserve TextBox(1 To Count)

Could you please help me in correcting this error.
Thanks a lot in advance

Hi,
Do you have this line at TOP of your forms code page

Dim TextBox() As New TextBoxClass

and did you Rename the Class Module TextBoxClass


Dave

 

[KTSARA]

Hi,
Do you have this line at TOP of your forms code page

Dim TextBox() As New TextBoxClass

and did you Rename the Class Module TextBoxClass


Dave

Hello Dave,
Oh! I am so stupid. I placed this line at top of the Userform_Initialize code.

Dim TextBox() As New TextBoxClass

This works nicely. Sorry for taking extra time from you because of my stupidity. You indeed are a genius.
Thank you so much for your time and effort

 

[dmt32]

Hello Dave,
Oh! I am so stupid. I placed this line at top of the Userform_Initialize code.

Dim TextBox() As New TextBoxClass

This works nicely. Sorry for taking extra time from you because of my stupidity. You indeed are a genius.
Thank you so much for your time and effort

Glad you have it working & solution does what you want

If wanted, you could add ControlTipText to the code to prompt users as they hover over each control to enter Numeric value only

Private Sub UserForm_Initialize()
    Dim Count As Integer
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            If Ctrl.Name Like "*Quantity*" Then
            Count = Count + 1
            ReDim Preserve TextBox(1 To Count)
                Set TextBox(Count).TextBoxClass = Ctrl
                Ctrl.ControlTipText = Ctrl.Name & " - Enter Numeric Value Only"
            End If
        End If
    Next
End Sub

add the line shown in BOLD & see what you think

Many thanks for kind feedback

Dave

 

[KTSARA]

Glad you have it working & solution does what you want

If wanted, you could add ControlTipText to the code to prompt users as they hover over each control to enter Numeric value only

Private Sub UserForm_Initialize()
    Dim Count As Integer
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            If Ctrl.Name Like "*Quantity*" Then
            Count = Count + 1
            ReDim Preserve TextBox(1 To Count)
                Set TextBox(Count).TextBoxClass = Ctrl
                Ctrl.ControlTipText = Ctrl.Name & " - Enter Numeric Value Only"
            End If
        End If
    Next
End Sub

add the line shown in BOLD & see what you think

Many thanks for kind feedback

Dave

Hello Dave,
It is nice. Thanks again for your kind replies.