Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validating entered data as date
I ahve some code (A couple Subs) that I use to validate
that the data entered into a specific column of a specific sheet is numeric, as listed below::: Sub Auto_Open() Sheets("Form").OnEntry = "numbers" End Sub Sub numbers() If ActiveCell.Column = 6 And Not IsNumeric (ActiveCell.Value) Then MsgBox "Value Must Be A Number." ActiveCell.Value = "" ' Clears contents of active cell. End If End Sub What I would like to be able to do is to change the validation process to make it validdate that the data entered is a date in mm/dd/yyyy format. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validating entered data as date
If ActiveCell.Text Like "*/*/????*" And IsDate(ActiveCell.Text) Then
MsgBox "OK" Else MsgBox "Not OK" End If However, some people might have their system date set as ##-##-## so this would fail. I would personally just use IsDate e.g. If IsDate(ActiveCell.Text) Then MsgBox "OK" Else MsgBox "Not OK" End If "Mr. B" wrote in message ... I ahve some code (A couple Subs) that I use to validate that the data entered into a specific column of a specific sheet is numeric, as listed below::: Sub Auto_Open() Sheets("Form").OnEntry = "numbers" End Sub Sub numbers() If ActiveCell.Column = 6 And Not IsNumeric (ActiveCell.Value) Then MsgBox "Value Must Be A Number." ActiveCell.Value = "" ' Clears contents of active cell. End If End Sub What I would like to be able to do is to change the validation process to make it validdate that the data entered is a date in mm/dd/yyyy format. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validating entered data as date
Dates are stored as the number of days since a base date. If excel
recognizes it as a date, then it is converted to that format. You use formatting to determine how it is displayed. You desire to check how it is typed in is unwarranted. -- Regards, Tom Ogilvy "Mr. B" wrote in message ... I ahve some code (A couple Subs) that I use to validate that the data entered into a specific column of a specific sheet is numeric, as listed below::: Sub Auto_Open() Sheets("Form").OnEntry = "numbers" End Sub Sub numbers() If ActiveCell.Column = 6 And Not IsNumeric (ActiveCell.Value) Then MsgBox "Value Must Be A Number." ActiveCell.Value = "" ' Clears contents of active cell. End If End Sub What I would like to be able to do is to change the validation process to make it validdate that the data entered is a date in mm/dd/yyyy format. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting according to the date the data is entered | Excel Discussion (Misc queries) | |||
static date in new file when data entered in another | Excel Worksheet Functions | |||
Validating Dates Entered | Excel Discussion (Misc queries) | |||
Validating a date field | Excel Discussion (Misc queries) | |||
Input Date when data is entered into another cell | Excel Worksheet Functions |