Restrict Textbox Text to Numbers Only (Visual Basic)

Introduction

    So the idea here is that you want to add a text box to a userform, but need validation code which forbids certain characters, while allowing some others. As simple as that sounds, it can actually get pretty tricky. Writing too much code into the subroutine, or function will slow it down, and in turn cause flickers or pauses… not something we want happening. So therefore, here especially there is a need for a slim enough function that it can be run once on every keystroke without affecting preformance.

    In the following guide, I have included a sample of the steps nessessary to create what is often termed a “dhtranslate function”. Though truely, there is no one such function as the task at hand may be accomplished in a multitude of ways. This is just one technique…the one I happen to favor the most. And all you really need to do is create an Excel userform with a textbox. Then just throw this code into it and see what it does. If you have any questions, please feel free to comment on the guide.

Download Sample Code

Special Thanks to Datafilehost.com for providing a service that allows me to make clean code available for download. You Rock!

Function DecimalOnly

’NOTE: Please download code samples. _
Guidespot uses characters that are incompatable _
with your compiler.

Public Function DecimalOnly(ByVal Text) _
As String
    Dim AllowedChr _
    As String

    AllowedChr = _
    “48|49|50|51|52|53|54|55|56|57”
    ’0  1  2  3  4  5  6  7  8  9

    Dim IllegalChr _
    As Boolean
    IllegalChr = True

    If Text = "" Then
     ’No need to parse
        Exit Function
    End If

    For ct = 0 To _
    UBound(Split(AllowedChr, “|”))
        If Not Len(Text) = 1 Then

            ‘If the character code equals something _
             in the list it’s ok
            
            If Asc(Mid(Text, Len(Text), 1)) = _
                Split(AllowedChr, “|”)(ct) Then
                    IllegalChr = False
                    Exit For
            End If
           
        Else
            If Asc(Text) = _
            Split(AllowedChr, “|”)(ct) Then
                    IllegalChr = False
                    Exit For
            End If
        End If
    Next

    ’Ignoring when len is 1 forbids _
    a period as the first character

    If Not Len(Text) = 1 Then
        If Not Len(Replace(Text, “.”, "")) = _
        Len(Text) – 2 Then
            If Mid(Text, Len(Text), 1) = _
            “.” Then
                IllegalChr = _
                False
            End If
        End If
    End If

    If IllegalChr Then
        Text = Mid(Text, 1, Len(Text) – 1)
    End If

    DecimalOnly = Text
End Function