CODEWITHSUNDEEP
vbaexcel
AddyProg
AddyProg

Reputation: 3050

Making VBA Form TextBox accept Numbers only (including +, - and .)

I have simple textBox and I want to validate its input including "+" , "-" and "." here is what I have tried

Private Sub DisplayValue_TextBox_Change()
If Not IsNumeric(DisplayValue_TextBox.Value) Then
               MsgBox "Only numbers allowed"

      End If
End Sub

But this only accepts numbers 0-9 no negative, positive value or float value..

Upvotes: 21

Views: 160912

Answers (8)

M King
M King

Reputation: 1

FWIW, I was fine with the OPs limitations on the type of values accepted but wasn't fine with the msgbox arriving at inappropriate times (such as when Backspace was used). To resolve that, I created a calculated field in the worksheet ("Value_AnyGood") that used the ISNUMBER function on the entered data. I then tested that for "True" on any event that left the UF where the data was entered. That resulted in a process that caught any non-numeric entries but didn't pop up a message for legitimate editing efforts. If the value was False, it lets the user know and then ends the routine with the current UF displayed.

Upvotes: 0

joseph.T
joseph.T

Reputation: 1

Private Sub TbCout_D_Edlp_Change()

Dim NotNumeric As Boolean

Dim TempValue As String

     If Not IsNumeric(TbCout_D_Edlp.Value) Then
 
        If TbCout_D_Edlp.Value <> "" Then
        
              NotNumeric = True
              
              MsgBox "Only numbers allowed"
              
              TempValue = Left(Me.TbCout_D_Edlp.Value, Len(Me.TbCout_D_Edlp.Value) - 1)
              
              While NotNumeric = True And TempValue <> ""
              
                   If Not IsNumeric(TempValue) Then
              
                        TempValue = Left(TempValue, Len(TempValue) - 1)
                        
                   
                   Else
                        
                        NotNumeric = False
                        
                   End If
        
              Wend
              
              Me.TbCout_D_Edlp.Value = TempValue
                
        End If
        
     End If

End Sub

Upvotes: 0

PEARL FUELS BPCL OUTLET
PEARL FUELS BPCL OUTLET

Reputation: 21

If TextBox1.Value <> "" Then
    Dim N As Boolean
    N = True
    Do While N
        If Not IsNumeric(TextBox1.Value) Then
            TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
        Else
            N = False
        End If
    Loop
End If

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

I use this. It will allow only numbers with decimals.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case vbKey0 To vbKey9, vbKeyBack, vbKeyClear, vbKeyDelete, _
        vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown, vbKeyTab
            If KeyAscii = 46 Then If InStr(1, TextBox1.Text, ".") Then KeyAscii = 0
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

Upvotes: 3

Lam Kent
Lam Kent

Reputation: 11

Im using that: 

Private Sub txtGiaNet_Change()
    If IsNumeric(txtGiaNet.Value) Then
        //if number do sth
    Else
        //if not, delete this character
        txtGiaNet.Value = Left(txtGiaNet.Value, Len(txtGiaNet.Value) - 1)
    End If

End Sub

Upvotes: 1

blackworx
blackworx

Reputation: 567

Having relied up till now on string parsing to do this job, I'm glad I decided to check and see how other people do it and found this Q.

I've refined Ruben Alvarez's excellent answer. The below will allow numerical entries only, and only one decimal point.

Private Sub txtShift1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii
        Case 46
            If InStr(1, txtShift1, ".") > 0 Then KeyAscii = 0
        Case 48 To 57
        Case Else
            KeyAscii = 0
    End Select

End Sub

This could be further refined to allow only a single "+", "-" etc. as necessary.

Upvotes: 13

Ruben Alvarez
Ruben Alvarez

Reputation: 251

use the KeyPress event, and discard any non-numeric entry:

Private Sub txtShift1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Debug.Print KeyAscii
If KeyAscii >= 48 And KeyAscii <= 57 Then
    Debug.Print "number"
Else
    Debug.Print "other"
    KeyAscii = 0
End If
End Sub

Upvotes: 25

user2140173
user2140173

Reputation:

Further to my comment:

Consider a sample Userform1 with a Textbox1 and a CommandButton1

enter image description here

when you enter anything in the TextBox1 the change event fires - ie. typing one character fires the Change() event and passes the current value so even when you type in the negative sign your current logic fails.

What you need is to use another event like _AfterUpdate() or _Exit() with an amphasis on the second one because your can cancel the event :)

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsNumeric(TextBox1.Value) Then
        MsgBox "only numbers allowed"
        Cancel = True
    End If
End Sub

You can find events here:

enter image description here

Upvotes: 24

Related Questions