Sorry, I misread "data" as date in the subject.
You could set up Data>Validation rules on the worksheet.
Or if you wish to use a class module, you would have easier maintenance in
the events that the criteria change; you would have to change only one
place. A brief example below.
You could pass the whole range to the class instead (or an array of values),
improve the criteria detection, input checking that a single cell is passed,
include more properties so it can be configured better, return an error
string or Raise an error upon failure...
Depends how complex you wish to get.
NickHK
<cDataValidation>
Private Const AllowedEntries As String = "Inv, "
Public Function ValidateData(StartCell As Range) As Boolean 'Or String Or
Long
ValidateData = False
With StartCell
If .Value > .Offset(0, 1).Value Then 'Is Date greater the next cell ?
If InStr(.Offset(0, 2).Value, AllowedEntries) > 0 Then 'Is Inv or
blank ?
If .Offset(0, 3).Value >= 0 Then 'Is +ve ?
ValidateData = True
End If
End If
End If
End With
End Function
</cDataValidation>
<Worksheet>
Private Sub cmdValidate_Click()
Dim cell As Range
Dim MyValidation As cDataValidation
Set MyValidation = New cDataValidation
For Each cell In Range("A1:A100")
With MyValidation
If .ValidateData(cell) = False Then MsgBox "Error in row : " &
cell.Address
End With
Next
End Sub
</Worksheet>
"ron" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Nick, thanks for the reponse. It is not just about date. The issue is,
> I will receive a sheet and need to verify that all the fields, such as:
>
> one date field needs to be greater than another date field
> One text field must be value of "Inv" or blank, but nothing else
> One numerical field must be positive number,
>
> etc.
>
> I need to make sure the sheet I received satisfies all the conditions.
> Otherwise it will highlight the cells which do not.
>
> thanks,
>
>
> Ron
>
>
> NickHK wrote:
> > Given the number of formats people may want to input dates, you will
make
> > your own life more easy if you provide them with a calendar control.
> > This should get you started :
> >
http://www.google.co.uk/search?hl=en...e+Search&meta=
> >
> > NickHK
> >
> > "ron" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > I have an input sheet which received the data from someone else.
> > >
> > > All the columns have some restriction, such as one date field must be
> > > greater than another date field, etc.
> > >
> > > I need to check the data integrity based on the data rules I have for
> > > each column. If the data is not satisfied, it will pop up some warning
> > > messages, or highlight the cells.
> > >
> > > Are there any sample codes that I can borrow?
> > >
> > > Appreciate your help greatly,
> > >
> > >
> > > Ron
> > >
>
Similar Threads
