CODEWITHSUNDEEP
vbaexcel
Asparagus
Asparagus

Reputation: 35

Check TextBoxes in Userform to make sure that they are integers

I'm trying to check my Userform when a command button is clicked to ensure that all the values entered in textboxes are integers. However, right now it's popping up with the message box even if I enter an integer value (i.e. 1 for all the textboxes).

Here's my code right now:

'Store inputs when clicking Continue
Private Sub Continue1_Click()

    'Define variables
    Dim Ctl As Control

    For Each Ctl In CategoriesForm.Controls
        If TypeName(Ctl) = "TextBox" Then
            If IsNumeric(Ctl.Value) = True Then
                If Int(Ctl.Value) <> Ctl.Value Then
                    MsgBox ("All inputs need to be integers for calculations to work.  Please check the values."), vbCritical, "Error: Not All Inputs are Integers"
                    Exit Sub
                End If
            Else
                MsgBox ("All inputs need to be integers for calculations to work.  Please check the values."), vbCritical, "Error: Not All Inputs are Integers"
                Exit Sub
            End If
        End If
    Next

    NumberClamps = Number_Clamps.Value
    NumberBrackets = Number_Brackets.Value
    NumberWashers = Number_Washers.Value
    NumberScrews = Number_Screws.Value
    NumberNuts = Number_Nuts.Value
    NumberUNuts = Number_UNuts.Value
    NumberRivets = Number_Rivets.Value
    NumberStuds = Number_Studs.Value
    Unload CategoriesForm

End Sub

Thanks

Upvotes: 0

Views: 3156

Answers (2)

Asparagus
Asparagus

Reputation: 35

In conjunction with Doug Coats Solution

Create Class Module called "CTextboxes"

Option Explicit

Public WithEvents TextGroup As MSForms.TextBox


'Sub to allow users to only enter integer values
Private Sub TextGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
  Case vbKey0 To vbKey9
  Case Else
    KeyAscii = 0
    Beep
    MsgBox ("All inputs need to be integers for calculations to work.  Please check the values."), vbCritical, "Error: All Inputs must be Integers"
End Select

End Sub

Put this code in the UserForm code module for when the USerForm initializes

Private Sub UserForm_Initialize()

'Stop user from entering non-integer values
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

Finally define this variable at the top of the UserForm Code

Option Explicit

Dim TextBoxes() As New CTextboxes

Upvotes: 0

Doug Coats
Doug Coats

Reputation: 7107

this is a sub that only allows user to enter numeric keys

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
      Case vbKey0 To vbKey9
      Case Else
        KeyAscii = 0
        Beep
    End Select
End Sub

Upvotes: 3

Related Questions