Automatic rounding down number at cell entering

[Hidden Dan]

In a financial spreadsheet I have some ranges where user can type in financial values with 2 decimal places. But I want them all be automaticly rounded down to zero decimals. This should happen after the value has been typed in.

Examples
1,250.23 --> 1,250
1,250.81 --> 1,250


Is there a VBA script that can perform such a task ?


Thanks, Dan

 

[Joe4]

If these values are being entered manually one-by-one, you can use a Worksheet_Change event procedure to do this, which runs automatically as they are entering data.
Simply right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 And IsNumeric(Target) Then
        If Target.Column = 4 Then
            Application.EnableEvents = False
            Target = Int(Target)
            Application.EnableEvents = True
        End If
    End If
End Sub

This example is set to work for column D. Just change this line for any other column:

        If Target.Column = 4 Then

Note that "A"=1, "B"=2, "C"=3, "D"=4, etc.

 

[Hidden Dan]

Thans Joe,

I made a simple try and it works. Smart how value is truncated to the integer value.

May I ask you 1 additional question ? How would I make this script running for 1 named range in a sheet instead of a Column ?


Thanks, Dan

 

[Joe4]

Use this variation (in this example, my range is named "MyRange"):

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    Dim cell As Range
    
'   See if updated cell in range named "MyRange"
    Set rng = Intersect(Target, Range("MyRange"))
    If Not rng Is Nothing Then
        For Each cell In rng
            Application.EnableEvents = False
            cell = Int(cell)
            Application.EnableEvents = True
        Next cell
    End If
    
End Sub

 

[Hidden Dan]

Excellent script. Thank you very much.

Dan

 

[Joe4]

You are welcome.