Data validation with VBA
 

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

Data validation with VBA
 

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

-----Original Message-----
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


.

Data validation with VBA
 

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)

"W. Adam" wrote in message
...
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

Data validation with VBA
 

"W. Adam" wrote:

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.


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

This is cleaner.

Regards,

John

Data validation with VBA
 

"W. Adam" wrote:

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 ...........................

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

This is cleaner.

Regards,

John