Data validation with VBA

W

W. Adam

I have a cell that data put into it needs to be validated, with upper and
lower limits can't use data validation, it has to be VBA code). Need to
write click event which will control data input within the limits, for
example:
Input box can only accept between 15 and 75, any idea how to write the code.
Do while numbers ...........................


Loop

Thanks for your help
 
J

Jason Morin

Who says you can't set upper and lower limits using Data
Validation?

Data > Validation,
Allow: Custom,
Formula:

=AND(A1>=15,A1<=75)

HTH
Jason
Atlanta, GA
 
B

Bob Phillips

Here is some worksheet event code that traps the input. Put it in the
worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
If .Value < 15 Or .Value > 75 Then
MsgBox "Invalid value"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

John Dougrez-Lewis

W. Adam said:
I have a cell that data put into it needs to be validated, with upper and
lower limits can't use data validation, it has to be VBA code). Need to
write click event which will control data input within the limits, for
example:
Input box can only accept between 15 and 75, any idea how to write the code.
Click to expand...


In VBA, you can use the Range's Validation object.

This is cleaner.

Regards,

John
 
J

John Dougrez-Lewis

W. Adam said:
I have a cell that data put into it needs to be validated, with upper and
lower limits can't use data validation, it has to be VBA code). Need to
write click event which will control data input within the limits, for
example:
Input box can only accept between 15 and 75, any idea how to write the code.
Do while numbers ...........................
Click to expand...

In VBA, you can use the Range's Validation object.

This is cleaner.

Regards,

John
 
Top