Removing Decimal from number by VBA Excel

Question

I wrote a bit of VBA code in Excel to remove the decimal separator in a textbox that contains a number. The code is as follows:

Private Sub TextBox1_Change()

TextBox1 = Format(TextBox1, "Standard")

End Sub

But it doesn't work properly. The final result has the separator for thousand and also has the decimal place.

This TextBox is not an input data entrance, in fact, it is an output display for a hidden cell. It related to a cell ("B15") that the cell is set on Separator Thousand Group (On) and No Decimal Place (Off). But the TextBox1 shows the Numbers with Separator T.Group (On) with Decimal Place (On). I need a syntax of VB code for keeping Separator T.Group without Decimal Place. Any idea for removing the decimal separator is welcome!

Answer 1

Assuming the user is providing the input, this is an X-Y problem IMO: your goal is to ensure your user can only type digit characters in that textbox, not to truncate decimals.

The problem is that a textbox' Change event is fired too late to do that. Instead, handle KeyDown - I'd do something like this:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Not KeyCodeValidator.IsValidDigitInput(KeyCode.value) Then KeyCode.value = 0
End Sub

Have a KeyCodeValidator standard module:

Option Explicit
Option Private Module

Public Function IsValidAlphaInput(ByVal keyAscii As Long, Optional ByVal allowDeletion As Boolean = True, Optional ByVal allowNav As Boolean = True) As Boolean
    IsValidAlphaInput = (keyAscii >= vbKeyA And keyAscii <= vbKeyZ) Or (allowDeletion And IsDeletion(keyAscii)) Or (allowNav And IsNavKey(keyAscii))
End Function

Public Function IsValidDigitInput(ByVal keyAscii As Long, Optional ByVal allowDeletion As Boolean = True, Optional ByVal allowNav As Boolean = True) As Boolean
    IsValidDigitInput = (keyAscii >= vbKey0 And keyAscii <= vbKey9) Or (keyAscii >= vbKeyNumpad0 And keyAscii <= vbKeyNumpad9) Or (allowDeletion And IsDeletion(keyAscii)) Or (allowNav And IsNavKey(keyAscii))
End Function

Public Function IsValidAlphanumericInput(ByVal keyAscii As Long, Optional ByVal allowDeletion As Boolean = True, Optional ByVal allowNav As Boolean = True) As Boolean
    IsValidAlphanumericInput = IsValidAlphaInput(keyAscii) Or IsValidDigitInput(keyAscii) Or (allowDeletion And IsDeletion(keyAscii)) Or (allowNav And IsNavKey(keyAscii))
End Function

Public Function IsValidDecimalInput(ByVal keyAscii As Long, ByVal contents As String) As Boolean
    If IsValidDigitInput(keyAscii) Or keyAscii = Asc(".") Then
        IsValidDecimalInput = IsNumeric(contents & Chr$(keyAscii))
    End If
End Function

Private Function IsDeletion(ByVal keyAscii As Long) As Boolean
    IsDeletion = keyAscii = vbKeyDelete Or keyAscii = vbKeyBack
End Function

Private Function IsNavKey(ByVal keyAscii As Long) As Boolean
    IsNavKey = keyAscii = vbKeyTab Or keyAscii = vbKeyLeft Or keyAscii = vbKeyRight
End Function

If you wanted to support decimals, you'd pass the current contents of your textbox to IsValidDecimalInput (which needs minor adjustments to support international decimal separators).

I actually have that module as a class, but a standard module fits the bill just fine too.

Point being, don't fix your user's input after it's in the textbox - prevent the user's invalid input from even being entered in the first place instead.

Answer 2

Note that both methods (Format from the comments on OP, and Split from @Gary's Student) effectively prevent the user from inputting the decimal, but they don't truncate or round the (presumably numeric) value, so if the user is trying to enter 123.54 the resulting value will be 12354 when probably you want either of 124 (round) or 123 (trunc).

Relying on _Change event for validation has some limitations :D and probably this value should be validated/manipulated by whatever other procedure is using it as an input.

Example:

Option Explicit
Private Sub CommandButton1_Click()
    Call ValidateMe(TextBox1)
    MsgBox (TextBox1.Value)

End Sub

Private Sub TextBox1_Change()
    ' do nothing
End Sub

Private Sub ValidateMe(obj As MSForms.TextBox)
    obj.Value = Format(obj, "0")
End Sub

Answer 3

If TextBox1 is a String like:

1,234.56

then use something like:

TextBox1 = Split(TextBox1,".")(0)

EDIT#1:

Based on the Comments,this should be used:

TextBox1 = Split(TextBox1 & Application.DecimalSeparator, Application.DecimalSeparator)(0)

This will:

1. return a null string given a null string
2. return a null string if there is no integer part
3. return the integer part in a Locale-independent fashion

Answer 4

braX sent this code:

TextBox1.Text = Format(TextBox1.Text, "#,###,###,##0")

Well done braX ,, Thank you, it's worked properly ... :)