Validating Numbers in Text boxes

Does somebody have a sub or function to force user only to enter numbers including decimals, eg 345.567 in a text box?

Thank you
bemsonAsked:
Who is Participating?
 
karlwwConnect With a Mentor Commented:
Make your own ActiveX control out of a text box and be done once and for all.  I'll e-mail you some code if you'd like.
0
 
paulstampCommented:
Create a project with a text box text1 and paste the following code in :

Private Sub Text1_KeyPress(KeyAscii As Integer)

If KeyAscii <> Asc(".") and KeyAscii <> 8 Then
    If KeyAscii > Asc("9") Or KeyAscii < Asc("0") Then
        KeyAscii = 0
    End If
End If

End Sub

Note - I've used the ASC(".") style functions in for illustrative purposes - you could replace these with the relevant constants ("." = 46, "9" = 57 and 0 = 48), ie :

If KeyAscii <> 46 and KeyAscii <> 8 Then
    If KeyAscii > 57 Or KeyAscii < 48 Then
        KeyAscii = 0
    End If
End If


The Check for KeyAscii allows the backspace key to operate.

0
 
RuchiCommented:
Karlww: don't need to make an ACtive X control for that!!

Here's very simple!

Option Explicit

Private Sub Text1_KeyPress(KeyAscii As Integer)
    If ((KeyAscii >= vbKey0) And (KeyAscii <= vbKey9)) Or (KeyAscii = vbKeyBack) Then 'numeric key
        Exit Sub
    ElseIf KeyAscii = 46 Then ' decimal point
        Exit Sub
    Else
        KeyAscii = 0
    End If
End Sub
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
hesCommented:
Or as:

Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii > 47 And KeyAscii < 58 Or KeyAscii = 46 Then
Else
  KeyAscii = 0
End If
End Sub
0
 
paulstampCommented:
hes... small point but your solution prevents the user using backspace !
0
 
paulstampCommented:
hes... small point but your solution prevents the user using backspace !
0
 
jawmwcCommented:
In the Textx_KeyPress
 
  "To include decimal point"
     If KeyAscii = 48 then exit sub

  "For Numbers Only"

     If KeyAscii < 48 or KeyAscii > 57 then

    MsgBox "Only Numbers permitted",0

    KeyAscii = 0
    Textx.SetFocus
    Exit Sub
    End If

0
 
bemsonAuthor Commented:
Code for activex would be appreciated,

between your answer & other comments I have a solution,

Many thnaks
0
 
bemsonAuthor Commented:
Code for activex would be appreciated,

between your answer & other comments I have a solution,

Many thnaks
0
 
bemsonAuthor Commented:
Code for activex would be appreciated,

between your answer & other comments I have a solution,

Many thnaks
0
 
paulstampCommented:
As Ruchi says... its a bit of an overhead to make an ActiveX control just for that (additional dependencies, problems with compatibility etc etc), but if you really feel the need :

1Create a new ActiveX control project and place a textbox on it.

0
 
paulstampCommented:
Sorry.. accidentally sent half cooked comment. Continue from 1 above to :

2) Load the Microsoft ActiveX Control Interface Wizard - find it in AddIn Manager and check the Loaded box

3) Map the properties of the textbox to the properties of your control so that you are effectively delegating the interface of your control to the underlying textbox.

4) Add the code as above (make sure to pick an example that includes support for the backspace)

As mentioned before... its a bit of a sledgehammer to crack a wallnut - I would only recommend this approach if you needa lot of these numeric-only textboxes.
0
 
karlwwCommented:
I mention ActiveX control because you are not trapping the pasting of non text data into the text box (users will try anything).  Also, what about negative numbers?  You need to include the minus sign.

Here's a brief snipit from my own ActiveX control to control pasting.
Const EM_UNDO = &HC7

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, wParam As Any, lParam As Any) As Long

Private Sub txtNumber_Change()
Dim rc As Long
    If Len(txtNumber) > 0 Then
        If txtNumber = "." Or txtNumber = "-" Or txtNumber = "-." Then Exit Sub
        If Not IsNumeric(txtNumber) Then
            rc = SendMessage(txtNumber.hwnd, EM_UNDO, ByVal 0&, ByVal 0&)
        Else
            If txtNumber = vbNullString Then
                mdValue = 0
            Else
                mdValue = CDbl(txtNumber)
            End If
        End If
    End If
End Sub
0
 
karlwwCommented:
I got cut off too above.  The ActiveX advantage is you do this once and you re-use it in all your projects.

HTH
0
 
bemsonAuthor Commented:
Thanks to all contributors to this topic.

I hope other users have also gained some new skills from this as I have.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.