2

I need to create a table where the operator can input numbers. This is a protected sheet, so the operator can not change anything, they can just input numbers in specific cells. [Microsoft 365 for Mac]

I need Excel to show 2 decimal places without rounding them. For example, for 3.888, I need 3.88 rather than 3.89.

For example, I need to have A1:A10 blank for the operator to enter the numbers and the VBA should be applied to those cells.

Does anyone know how to do that with VBA?

Thanks

Need to implement VBA to truncate the entry data.

Milad
  • 33
  • 4

1 Answers1

3

A Worksheet Change: Automatically Truncate Decimals

Edit

  • You can find an improvement on the code here.

  • This code fails with a higher number of decimals. The following corrections should be applied:

    • Remove the lines Dim Num As Long: Num = 10 ^ DECIMAL_PLACES.
    • Replace the line dValue = Int(iValue * Num) / Num with dValue = Application.RoundDown(iValue, DECIMAL_PLACES).
    • Replace the line If dValue < iValue Then with If dValue <> iValue Then.

Original Post

  • Copy the code into the sheet module (e.g. Sheet1) of the worksheet where it needs to be applied (not into a standard module, e.g. Module1 and not into the ThisWorkbook module).
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const TARGET_RANGE As String = "A1:A10"
    Const DECIMAL_PLACES As Long = 2

    On Error GoTo ClearError
    
    Dim irg As Range: Set irg = Intersect(Me.Range(TARGET_RANGE), Target)
    If irg Is Nothing Then Exit Sub

    Dim Num As Long: Num = 10 ^ DECIMAL_PLACES
    
    Application.EnableEvents = False
    
    Dim iCell As Range, iValue, dValue As Double
    
    For Each iCell In irg.Cells
        iValue = iCell.Value
        If VarType(iValue) = vbDouble Then
            dValue = Int(iValue * Num) / Num
            If dValue < iValue Then
                iCell.Value = dValue
            End If
        End If
    Next iCell
    
ProcExit:
    On Error Resume Next
        If Not Application.EnableEvents Then Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub
ClearError:
    Resume ProcExit
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • thanks for the code. But when I run it, I get this error: 'Argument not optional' – Milad Jun 26 '23 at 23:57
  • 1
    You have to copy the code to the correct place: in the VBE, open the `Project Explorer` and double-click on the correct sheet which will open the correct window. Also, this code runs automatically each time you change a value in `A1:A10`: there's nothing to run. See the instructions with an image in [this recent post](https://stackoverflow.com/a/76544383). – VBasic2008 Jun 27 '23 at 00:08
  • Now it does work. But can I make changes to the range? For example, ["B1:B10", "C11:C20"]? – Milad Jun 27 '23 at 00:11
  • 2
    Sure, use `Const TARGET_RANGE As String = "B1:B10,C11:C20"`. – VBasic2008 Jun 27 '23 at 00:12
  • Just a question, the code works fine on the empty cells. But I tried to apply the code on a cell (lets call it B11) that has `IFERROR(AVERAGE(B1:B10),"")`. Then the code does not work. Is there any way to make the code applicable to the cells with formula? – Milad Jul 17 '23 at 23:55
  • You need to use different events e.g. a combination of the `Workbook_Open` and `Worksheet_Calculate` events but that's out of the scope of this question. Best ask another one explaining why you can't use e.g. `B1:B10` with a `Worksheet_Change` event. – VBasic2008 Jul 18 '23 at 05:29
  • just did. It's here: https://stackoverflow.com/questions/76713700/how-to-use-vba-in-excel-to-truncate-numbers-rather-than-rounding-in-cells-where – Milad Jul 18 '23 at 14:13
  • I came across an odd condition. when I use 1.11116, it gets rounded down to 1.11115. But this happens so rarely that I can't find a pattern. For example, 1.22226 is getting rounded down too, but 2.22226 is fine. Also, 17.84116 gets rounded down but 17.85116 is fine!! any ideas why some numbers get rounded, specifically the some that end at a 6? – Milad Jul 24 '23 at 18:35