Prev: MAX Function in VBE
Next: LOOKUP function help
From: Brad on 13 May 2010 11:41
Thanks for taking the time to read my question.

I want to write a function that is executed before the user exits a specific
cell. The purpose is to check the format of the date that has been entered,
and make the user correct it if it's wrong.
The reason is I'm getting stuff like:
Mar. 12-2010
12/Mar-10
10-12-Mar
12.2010.03

Some are date formats and some aren't even close...

Dates will be in a specific column or specific cell, so I need to execute
the function before the user leaves that cell or column. Not sure how to do
that. I know in MS Access there is a BeforeUpdate, but don't see anything
like that in Excel.

Thanks for your help

Brad
From: Dave Peterson on 13 May 2010 12:09
Maybe you don't need code.

Maybe data|Validation (specify a date and a range of valid dates) that can be used.

You could format the cell the way you like. I'd use an unambiguous date format
like: dd-mmm-yyyy

Be aware that Data|Validation can be broken pretty easily, but if the users are
typing (not copy|pasting), you may be ok.

======
That said. There is a worksheet_Change event that you may be able to tie into.

Brad wrote:

> Thanks for taking the time to read my question.
>
> I want to write a function that is executed before the user exits a specific
> cell. The purpose is to check the format of the date that has been entered,
> and make the user correct it if it's wrong.
> The reason is I'm getting stuff like:
> Mar. 12-2010
> 12/Mar-10
> 10-12-Mar
> 12.2010.03
>
> Some are date formats and some aren't even close...
>
> Dates will be in a specific column or specific cell, so I need to execute
> the function before the user leaves that cell or column. Not sure how to do
> that. I know in MS Access there is a BeforeUpdate, but don't see anything
> like that in Excel.
>
> Thanks for your help
>
> Brad

--

Dave Peterson
From: ryguy7272 on 13 May 2010 12:34
VBA Won't run inside of a cell. Take a look at this:
http://www.contextures.com/xlDataVal06.html#Date

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Brad" wrote:

> Thanks for taking the time to read my question.
>
> I want to write a function that is executed before the user exits a specific
> cell. The purpose is to check the format of the date that has been entered,
> and make the user correct it if it's wrong.
> The reason is I'm getting stuff like:
> Mar. 12-2010
> 12/Mar-10
> 10-12-Mar
> 12.2010.03
>
> Some are date formats and some aren't even close...
>
> Dates will be in a specific column or specific cell, so I need to execute
> the function before the user leaves that cell or column. Not sure how to do
> that. I know in MS Access there is a BeforeUpdate, but don't see anything
> like that in Excel.
>
> Thanks for your help
>
> Brad
 | 
Pages: 1
Prev: MAX Function in VBE
Next: LOOKUP function help