Date Validation Using VBA

Question

I want to validate that any entry in a column is a valid date, but a valid date using UK regional settings i.e dd/mm/yy.

IsDate() seems to recognise both 13/7/2010 and 7/13/2010 as valid dates, but the later is not in the UK, or for my purposes.  DateValue() does the same.

I have the procedure that traps the worksheet change event and tests any chnage to the required column, but cannot seem to be able to write any VBA code which passes 13/7/2010 and fails 7/13/2010.

I do not want to use data validation as I perform other checks in my code.

Answer 1

One way would be to from the developer menu insert the Calendar Control 11.0 from the ActiveX list, in its property box set the cell to have the date in it as its LinkedCell then enter the folowing code to the worksheet module:

Private Sub Calendar1_Click()

    Me.Calendar1.Visible = False

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("B5")) Is Nothing Then

    Me.Calendar1.Visible = True

    End If

End Sub

This will capture when the cell is selected (I chose B5 at random) bring up the calendar and then close when a date is selected. A way of forcing users to enter dates correctly...

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 2

Mike's question is the same one I was about to ask. The problem when you allow a user to type in a date is that you cannot tell if month/days less than 13 are in their correct order or not... you are stuck having to assume they will always enter data in the correct order. And you should not try to other month/day ordering because that will give your users a false sense of security when you correct one input and say nothing about another. If there is a problem with your users being able to enter dates correctly, then you should either give them a calendar to select from or give them separate, labeled fields for the month and day (and year).

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

Hi,

What do we do if we find a date

3/4/2010

is that 3 April or 4 March

Edit... I should have added that you can use this

IsNumeric(Range("A2").Value2)

which returns FALSE for 7/13/2010 but the main problem remains

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 4

Smart idea I will file this for future use.

In this case I do not think the user will want to have to pick from a date picker every time they change column dates, but your solution is a good one.

Answer 5

Thank you for the answer.

I am not interested in whether the user enters a valid incorrect date, i.e. determinging hwat they meant when they enetered 3/4/2010, I just wanted a way of stopping them entering invalid dates i.e. 7/13/2010.