Capping Numbers in Cells

  • Thread starter Thread starter ScS
  • Start date Start date
S

ScS

If I want to cap a number inside of a cell, how would I go
about doing that?

ex:I enter in 2567 but I want it to cap at 2000. Is this
possible?

scs
 
Use Data | Validation... | Settings tab | Decimal / less than or equal to /
2000

However, the user will be able to copy and paste "invalid" values into the
cell

Regards

Trevor
 
One easy method is to simply use a formula in another cell
to cap the number, then use this cell for all your
calculations.

A1: 2567
B1: =ROUNDDOWN(A1,-2) ---> 2000

Otherwise, you could use worksheet_change event in VBA.

HTH
Jason
Atlanta, GA
 
Hi
if I understood you correctly this can only be achieved with VBA (an
event procedure). Try the following code (put it in your worksheet
module - not in a standard module)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value >2000 Then
application.enableevents = false
.value = 2000
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
Trevor Shuttleworth said:
Use Data | Validation... | Settings tab | Decimal / less than or equal to /
2000

However, the user will be able to copy and paste "invalid" values into the
cell
Click to expand...

This seems to make the data "validation" somewhat useless.
Is there any way to trap the 'Paste' event?

I wonder if Microsoft will fix this 'feature'...

James
 
Back
Top