Calculation based on input from userform textbox

**h0ps** · 11-21-2012, 11:12 AM

Hi guys

I have an userform with a textbox. Based on the input I would like to make some calculations (the input has to be a number). How can I do this? I think that I have to change the input from text to a number, but I don't know how. I hope some of you guys can help me out

Thx a lot in advance!

**xLJer** · 11-21-2012, 10:14 PM

Here is a way to enforce numeric input into a text box.
Any other character will not appear in the text box; it will
be removed by the code after it is typed.

Private Sub TextBox1_Change()
Dim txtBoxText As String
    txtBoxText = TextBox1.Value
    
    If InStr("0123456789", Right(TextBox1.Value, 1)) = 0 Then
        TextBox1.Value = Mid(txtBoxText, 1, (Len(txtBoxText) - 1))
    End If

End Sub

This is how you might convert the text box value for use in a numeric calculation.

Private Sub CommandButton1_Click()

    If Len(TextBox1.Text) > 0 Then
        MsgBox "That number doubled is " & CLng(TextBox1.Text) * 2
    End If
    
End Sub

**mikerickson** · 11-21-2012, 10:41 PM

This will prevent the user from entering anything other than a non-negative integer.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = KeyAscii * -("1234567890" Like "*" & Chr(KeyAscii) & "*")
End Sub

If you want the user to be able to enter any number, you could use this kind of code.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newText As String
    
    With TextBox1
        newText = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
    End With
    
    KeyAscii = KeyAscii * -CLng(IsNumeric(newText & "0"))
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox1
        .Text = Format(Val(TextBox1.Text), "@")
    End With
End Sub

The BeforeUpdate event is to change the text that the user enters into a standard format.
This code will force a "general" format, so that if either "1.23000" or "123e-02" is entered, they will both be shown as "1.23" (after the focus leaves the textbox).
You could change that to any format that you prefer.

Note that the BeforeUpdate event uses the Val function to convert a string into a number.