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

Question

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..

Answer 1

Further to my comment:

Consider a sample Userform1 with a Textbox1 and a CommandButton1

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: