How to allow double only data type in textbox?

user_11711920 0
Viewed 148

I have a userform, where users are supposed to enter measured dimensions of a part (Quality Management field) into checkboxes. This means no text is allowed, neither some random digits, only numbers.

What I have now, is this:

Private Sub TextBox25_AfterUpdate()    
    If Not IsNumeric(TextBox25.Value) Then
        MsgBox "Invalid data!"
        TextBox25.BackColor = RGB(255, 200, 200)
        Cancel = True
    End If
End Sub

It's not perfect though, user still can type in some random digits like 09 instead of 0,9 and get no error message. I believe allowing only double-type data is the key but I tried the code below and it does not work (I get the error message every time, no matter the data type). Any ideas?

Private Sub TextBox19_AfterUpdate()
    If Not VarType(TextBox19.Value) = vbDouble Then
        MsgBox "Invalid data!"
        TextBox19.BackColor = RGB(255, 200, 200)
        Cancel = True
    End If
End Sub
vba excel
2 Answers

The .Value of a TextBox is always a String the name "TextBox" already includes that it is "Text". So it cannot be of type Double unless you take that String and convert it (implicit or explicit) into a Double.

VarType(TextBox19.Value) will always return vbString because it returns the type of the variable not the type of the data inside the variable.

So you actually need to test if it is decimal (not a integer).

The only way to test this properly is to check if the String contains exactly one , (respective . depending on your localization). And then test if this is numeric (otherwise it would accept a,b too).

Option Explicit

Public Sub TestForDecimalInput()
    Dim DecimalValue As Double
    Dim TextBoxValue As String

    TextBoxValue = "9" 'just for testing get your text box value here: TextBoxValue = TextBox19.Value

    'this replaces . and , with the actual decimal seperator of your operating system
    'so the user is allowed to either enter `0,9` or `0.9`
    TextBoxValue = Replace$(TextBoxValue, ".", Application.DecimalSeparator)
    TextBoxValue = Replace$(TextBoxValue, ",", Application.DecimalSeparator)

    'Check if there is exactly one! decimal seperator
    If Len(TextBoxValue) = Len(Replace$(TextBoxValue, Application.DecimalSeparator, "")) + 1 Then

        'we need to check for numeric too because yet it could be `a,b` too
        If IsNumeric(TextBoxValue) Then
            DecimalValue = CDbl(TextBoxValue)
        End If
    End If

    If DecimalValue <> 0 Then
        Debug.Print TextBoxValue, "->", DecimalValue
    Else
        Debug.Print TextBoxValue, "->", "Invalid Data"
    End If
End Sub

This would be the result of some example inputs

0.9           ->             0,9 
09            ->            Invalid Data
0,9           ->             0,9 
0,9,0         ->            Invalid Data
0,0           ->            Invalid Data
9,0           ->             9 
9             ->            Invalid Data

Note that 9,0 will be accepted as input but 9 will be invalid as input.

user_32196130

Try this. This will limit entires at runtime :)

'~~> Prevent anything other than numbers and decimals
Private Sub TextBox19_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, TextBox19.Text, ".") Then KeyAscii = 0
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

'~~> Allow only decimals
Private Sub TextBox19_AfterUpdate()
    If Int(Val(TextBox19.Value)) = TextBox19.Value And _
    InStr(1, TextBox19.Value, ".") = 0 Then
        MsgBox "Invalid data!"
        TextBox19.BackColor = RGB(255, 200, 200)
    End If
End Sub

Note: If you do not want to allow 9.0 then remove InStr(1, TextBox19.Value, ".") = 0 in the _AfterUpdate()

AND If you want to disable the inputs like 0x.xx then you can use this as well

Private Sub TextBox19_AfterUpdate()
    If Int(Val(TextBox19.Value)) = TextBox19.Value And _
       InStr(1, TextBox19.Value, ".") = 0 Or _
       (Left(TextBox19.Value, 1) = 0 And Mid(TextBox19.Value, 2, 1) <> ".") Then
        MsgBox "Invalid data!"
        TextBox19.BackColor = RGB(255, 200, 200)
    End If
End Sub
user_11405790
Related