Capping Numbers in Cells

[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

 

[Vasant Nanavati]

Not without using a macro.

 

[Trevor Shuttleworth]

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

 

[Jason Morin]

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

 

[Frank Kabel]

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

 

[James Minns]

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

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