Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Date validation code

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Paul
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,

    I've got three text boxes on a form, entitled "day", "month" and "year". I've managed to concactenate them and format them into a date that I can plug into Excel. However, there is nothing to stop the user selecting an invalid date, e.g. 31st April 2001.

    Does anybody have example code to allow only valid dates to be entered????

    Many Thanks in anticipation.

    Cheers - Paul

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One method I've used, is to use comboboxes instead of text boxes. Combos are a pain in the **** for the user, but can be a programmer's delight.

    However, to answer your question, you're going to have to create validation code yourself, but here's a format I suggest (I've assumed that your Month textbox is called txtMonth and the Day textbox is called txtDay):


    Select Case txtMonth.Text

    Case 1, 3, 5, 7, 8, 10, 12 '31 day months
    If txtDay.Text > 31 Then
    MsgBox "There aren't that many days!"
    End If
    Case 4, 6, 9, 11
    If txtDay.Text > 30 Then '30 day months
    MsgBox "There aren't that many days!"
    End If
    Case Else 'February
    'You're going to want to check for a leap year here.

    End Select


    Note:: I've used numbers for the months, you can put in the words or whatever instead. (just remember if you're using text use double quotes around them)

    I hope this is a good start for you.

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Paul
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks A Lot Mark - That's Great!

    I've stumbled upon the IsDate function - does that have any limitations?

    Cheers - Paul

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    Just a thought.

    You could ask the user to select the month first, then the year, finally the day.

    This order of selection would allow you to dynamically construct the list of the day numbers to be shown.

    The formula that follows would produce such a list. Put it in a cell in the first row where the other lists are:

    =IF(MONTH(DATE($B$1,$A$1,ROW()))=$A$1,DAY(DATE($B$1,$A$1,ROW())),"")

    to be copied down till row 31.

    A1 is where the month is selected and B1 is where the year is selected.

    You need to name this range using an OFFSET formula. You can then use the name so created as source list.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-02 09:28 ]

    [ This Message was edited by: Aladin Akyurek on 2002-05-02 09:29 ]

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-02 08:46, paulyf wrote:
    Thanks A Lot Mark - That's Great!

    I've stumbled upon the IsDate function - does that have any limitations?

    Cheers - Paul
    The only limitation that's immediately obvious to me is that it wont tell you what part of the date is wrong. (since it's a boolean check) Apart from that, there's no harm in using "IsDate".

  6. #6
    New Member
    Join Date
    Feb 2004
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date validation code

    I have the same thing in my budget progam. I use the validation box.

    After you set your range using setting click on the input message tab and put a message with your dropdown box, which you don't have to show. Just unclick "Show input message when cell is selected"

    Then select the error alert tab and put a message here also. Like the input message you can unshow this also.

    These will not let you to put anything in the cell unless its in the dropdown list.... hope this helps.......... qmp

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •