Automatic rounding down number at cell entering

Hidden Dan

Board Regular
Joined
Dec 7, 2016
Messages
61
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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:
Code:
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:
Code:
        If Target.Column = 4 Then
Note that "A"=1, "B"=2, "C"=3, "D"=4, etc.
 

Hidden Dan

Board Regular
Joined
Dec 7, 2016
Messages
61
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
Use this variation (in this example, my range is named "MyRange"):
Code:
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,198
Messages
5,442,987
Members
405,212
Latest member
Arnie58

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top