I need to add validation criteria to a cell (A2) that:
1. has no leading or trailing spaces (=A2=TRIM(A2))
AND
2. is eight digits long (both numeric and alpha)

but cannot get them both to operate!
Any help appreciated

Data > Validation... > Custom > Formula Is >
=AND(A2=TRIM(A2),(LEN(A2)=8))
--
Gary''s Student - gsnu200771

Will give it a go, thanks. Any thoughts on ensuring that a date has format
dd/mm/yyyy?

"Gary''s Student" wrote:

> Data > Validation... > Custom > Formula Is >
> =AND(A2=TRIM(A2),(LEN(A2)=8))
> --
> Gary''s Student - gsnu200771

enter following code in worksheet change event produre of the worksheet:


If Target.Address = "$A$2" Then
Target.Value = Trim(Target.Value)
If Len(Target.Value) <> 8 And Len(Target.Value) <> 0 Then
MsgBox "Please enter 8 character numeric or ctring in this cell!"
Target.ClearContents
Target.Select
End If

"Andy" wrote:

> I need to add validation criteria to a cell (A2) that:
> 1. has no leading or trailing spaces (=A2=TRIM(A2))
> AND
> 2. is eight digits long (both numeric and alpha)
>
> but cannot get them both to operate!
> Any help appreciated

First enter the following UDF:

Function HotDate(r As Range) As Boolean
HotDate = False
v = r.Text
If Len(v) <> 10 Then Exit Function
If Not IsNumeric(Replace(v, "/", "")) Then Exit Function
If Not (Mid(v, 3, 1) = "/" And Mid(v, 6, 1) = "/") Then Exit Function
HotDate = True
End Function

this will return True if the argument has the desired form. So in B2 ( or
any other cell) enter:

=HotDate(A2)

So B2 will always reflect if the entry in A2 has the correct form.

Finally we can use Data Validation. Set the Data Validation on A2 to
FormulaIs:

=B2

Sorry about having to use an extra cell, but I have had little luck using
UDFs in Data Validation without a helper cell. (and besides, cells are cheap)
--
Gary''s Student - gsnu200771


"Andy" wrote:

> Will give it a go, thanks. Any thoughts on ensuring that a date has format
> dd/mm/yyyy?
>
> "Gary''s Student" wrote:
>
> > Data > Validation... > Custom > Formula Is >
> > =AND(A2=TRIM(A2),(LEN(A2)=8))
> > --
> > Gary''s Student - gsnu200771

You are doing this at the spreadsheet level (as opposed to using VBA),
right? Why can't you just use Format Cells to force the display to that
format no matter how the user enters his/her date? Just to point out though,
you will not be able to insure the user is always putting in day followed by
month for day values less than or equal to 12 (in case that was the
motivation behind your question).

Rick

> Will give it a go, thanks. Any thoughts on ensuring that a date
> has format dd/mm/yyyy?
>
>> Data > Validation... > Custom > Formula Is >
>> =AND(A2=TRIM(A2),(LEN(A2)=8))
>> --
>> Gary''s Student - gsnu200771

How would I do it using VBA. I gave a macro which asks for date as Input from
user as a part of data collection. I need to make sure that

1. user does not leave it blank
2. user enters d-m-yy, or dd-mm-yy, or dd-mm-yyyy
3. on clicking o.k. the cell shows date as dd-mm-yyyy

any help would be much appreciated

ps: I have a opencalendar macro dionloaded and installed, but I do not know
how to call it from within another macro for date entry by selecting from
calendar display and then revert to the macro for other data entry.

"Rick Rothstein (MVP - VB)" wrote:

> You are doing this at the spreadsheet level (as opposed to using VBA),
> right? Why can't you just use Format Cells to force the display to that
> format no matter how the user enters his/her date? Just to point out though,
> you will not be able to insure the user is always putting in day followed by
> month for day values less than or equal to 12 (in case that was the
> motivation behind your question).
>
> Rick
>
> > Will give it a go, thanks. Any thoughts on ensuring that a date
> > has format dd/mm/yyyy?
> >
> >> Data > Validation... > Custom > Formula Is >
> >> =AND(A2=TRIM(A2),(LEN(A2)=8))
> >> --
> >> Gary''s Student - gsnu200771
>
>