3

I want to build a worksheet that when numbers are entered into a cell and the user ends the edit on that cell they are automatically rounded to the nearest 0.25. I do not want to have two columns, one for the original value and one for the rounded value. Is this possible and if so; how would one accomplish this?

This is what the workflow would be:

  • User types 2.30 in a cell
  • User tabs to the next cell or hits enter to leave the cell
  • Cell value changes from 2.3 to 2.25

I also want the value of the cell to be 2.25, I do not want the value to be 2.30 but formatted to appear as 2.25.

Aran Mulholland
  • 162

2 Answers2

2

It’s pretty easy in VBA.  Just create this subroutine:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Target.Value = Round(4 * Target.Value) / 4
    Application.EnableEvents = True
End Sub

Excel will call this routine whenever anything on the sheet changes.  The

Application.EnableEvents = False

statement temporarily turns off this automatic invocation, to prevent the Target.Value = … statement from causing the routine to be invoked recursively.  See How do I add VBA in MS Office? for general information on using VBA.

Community
  • 1
Scott - Слава Україні
  • 22,532
1

Closest I can get without VBA is using Data Validation. It won't change the value, but it will alert the user that they've entered an invalid value. Note you can "cheat" data validation in Excel by pasting from outside the data-validated range, so if it's vital then this probably isn't the ideal solution.

The cell in question should be selected, then go to Data / Validation, and choose Allow "Custom", and a formula of =MOD(A2,0.25)=0 (this formula is valid for cell A2). You can then go to the "Error Alert" tab and change the error message to "Please enter a value ending with 0.25, 0.5, 0.75 or a whole number in this cell"

Best way to push this to an entire column is by doing a single cell, then copying it to many other cells. The data validation should move accordingly (so it won't always be based on cell A2 - though this is presumably possible using $A$2)

Jon
  • 156