How to limit TextBox input to a numeric value?

Question

I have code that will create TextBoxes inside a Multipage:

Private Sub CommandButton1_Click()

RowChar = 70
MultiPage1.Pages.Clear

For i = 0 To TextBox1.Value - 1
    MultiPage1.Pages.Add
    MultiPage1.Pages(i).Caption = "Variable" & i + 1

    Call LabelPerPage

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "NameBox")
    With txtbx
        .Top = 20
        .Left = 100
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MinBox")
    With txtbx
        .Top = 50
        .Left = 100
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "LsbBox")
    With txtbx
        .Top = 20
        .Left = 300
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MaxBox")
    With txtbx
        .Top = 50
        .Left = 300
    End With

    If i = 0 Then
        FormulaString = "= C15"
    Else
        FormulaString = FormulaString & "  " & Chr(RowChar) & "15"
        RowChar = RowChar + 3
    End If
Next i
TextBox2.Value = FormulaString
End Sub

Private Sub LabelPerPage()
    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 20
        .Left = 50
        .Caption = "NAME:"
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 50
        .Left = 50
        .Caption = "MIN:"
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 20
        .Left = 250
        .Caption = "LSB:"
    End With

    Set txtbx = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.Label.1")
    With txtbx
        .Top = 50
        .Left = 250
        .Caption = "MAX:"
    End With
End Sub

I tried to create a page and also textbox in it, my problem is I cannot do KeyPress on a TextBox because it will only automatically create because of my code.

Goal:

1.) To do KeyPress were the TextBox cannot input a numberic value or Letter.

2.) I want to compare the two textboxes were textbox1 should be minimum to textbox2

I tried this:

Option 1:

Private Sub MaxBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 46 And KeyAscii < 58) Or KeyAscii = 43 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed, you can enter numbers only"
End If
End Sub

Option 2:

Private Sub OnlyNumbers()
    If TypeName(Me.ActiveControl) = "MaxBox" 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
End Sub

Can this If TypeName(Me.ActiveControl) = "MaxBox" Then ever be true? Would it not be TextBox? — Nathan_Sav
– Nathan_Sav, Commented Dec 10, 2018 at 9:00
The KeyPress one works for me, to only allow numbers. You need to add to this line If (KeyAscii > 46 And KeyAscii < 58) Or KeyAscii = 43 Then to check more, like If (KeyAscii > 46 And KeyAscii < 58) Or KeyAscii = 43 and me.textbox1.value<100 Then — Nathan_Sav
– Nathan_Sav, Commented Dec 10, 2018 at 9:04
Use a class with WithEvents, so in this case you have the Keypress code allready in your class — EvR
– EvR, Commented Dec 10, 2018 at 9:06
Keypress is fine. TypeName will never be "maxbox" so you'd have to capture the relevant control in some other way. Event sinking all textboxes might be the easiest way to do what you need. — Rik Sportel
– Rik Sportel, Commented Dec 10, 2018 at 9:07
@RikSportel what do you mean by Event sinking all TextBoxes? Can you please elaborate it? I also tried to modify the MaxBox to TextBox in the code If TypeName(Me.ActiveControl) = "TextBox " Then But still, it won't work. — marubindesu
– marubindesu, Commented Dec 10, 2018 at 9:26

Rik Sportel · Accepted Answer · 2018-12-10 10:06:50Z

Why not lose the keyPress altogether, since the only thing you're trying to achieve is Numeric Input only? You can just do something like this in your userform code:

Option Explicit
'Variable to capture Change event from your textbox:
Private WithEvents maxbox As MSForms.TextBox

'The creation of the thing; I just created a multipage control to reuse your lines.
Private Sub UserForm_Initialize()
    Dim txtbox as MSForms.TextBox
    Dim i As Integer
    i = 0
    Set txtbox = UserForm1.MultiPage1.Pages(i).Controls.Add("Forms.TextBox.1", "MaxBox")
    With txtbox 
        .Top = 50
        .Left = 50
    End With
    Set maxbox = txtbox
End Sub

'Capture change of maxbox:
Private Sub maxbox_Change()
    'In case somebody entered something non-numeric:   
    If IsNumeric(maxbox.Text) = False Then
        'Remove the input character
        maxbox.Text = Left(maxbox.Text, Len(maxbox.Text) - 1)
        'And alert the user
        MsgBox "numeric only!"
    End If
End Sub

If you need multiple, you can also just create a custom class where you capture the event, and add a collection of that class to the Userform. For doing that, you can have a look at the answers on this question

Edit: For the second half (validate against the Minbox) you can use the same event: Just add another if statement to ensure the numeric value is > CInt(minbox.text) (or another numeric type).

Edit 2: You might want to add error handling for the Left(maxbox.Text, Len(maxbox.Text) - 1) bit in case the Length of the string is 0 (i.e. when somebody pressed backspace / delete to trigger the change event).

Collectives™ on Stack Overflow

How to limit TextBox input to a numeric value?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related