Closed Thread
Results 1 to 25 of 25

Check Date Format Input into Excel Column with VBA code

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Check Date Format Input into Excel Column with VBA code

    Hi,

    I would like to check the format of dates entered or pasted into a Excel Spreadsheet column with VBA code.
    The date format must be yyyymmdd and could either be entered or pasted as a block of cells. i.e. It would be necessary to cycle thorugh the data.
    A message box should appear if any invalid data is found and the option to clear the individualy cell with the bad entry or cancel the entire input provided.

    Is this possible? Can anybody provide the VBA code for me to test?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Date Format Input into Excel Column with VBA code

    Hello stanlelma,

    Rather than using VBA, have you considered Data Validation?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Check Date Format Input into Excel Column with VBA code

    Thanks but Excel Data Validation does not prevent pasting of invalid data and this will be the most common input method.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Date Format Input into Excel Column with VBA code

    Hello stanlelma,

    Do you have a workbook you can post?

  5. #5
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Check Date Format Input into Excel Column with VBA code

    Here is an example
    Attached Files Attached Files

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Date Format Input into Excel Column with VBA code

    Hello stanlelma,

    I added 2 macros to the attached workbook. These will validate that a number entered or copied into columns "D" or "F" of the sheet are in YYYY MM DD format. If not, a message box ask the user if they want to clear the cell or not.
    Let me know if any changes need to be made.

    Worksheet Change Event Code
    Please Login or Register  to view this content.
    Module 3 Date Format Validation Macro
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Check Date Format Input into Excel Column with VBA code

    Thanks, This is a really cleaver why to code it. Just one small change that I can't get working myself.
    I need the validation to pevent non-numeric entries. For Example: "any text" or 26/06/2013.

    IsNumeric does not appear to prevent the text entry, but maybe I coded it wrong.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Date Format Input into Excel Column with VBA code

    Hello stanlelma,

    The macro will only return True if the argument passed into is a number, can be converted to a date, and matches your format. So, what is the problem?

  9. #9
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Check Date Format Input into Excel Column with VBA code

    If I try to copy text into the range I get a Run-time error '13'.

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Date Format Input into Excel Column with VBA code

    Hello stanlelma,

    Sorry about that oversight. It takes only a single line to fix the problem. Here is the revised and working code.
    Module3 Macro Code
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Check Date Format Input into Excel Column with VBA code

    Perfect! Thank you very much

  12. #12
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Check Date Format Input into Excel Column with VBA code

    Actually two last enhancement if you would be so kind.

    1) If I delete data from the range I get a Run-time error '13'.
    2) I would like to prevent update of "any" data into the range even if one cell is invalid. Therefore after the message about invalid date format(s) is displayed the "whole" range of input data should be cleared.

    Thanks again

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Date Format Input into Excel Column with VBA code

    Hello Stanlelma,

    I made the changes you requested. Please see the attached workbook for the code.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Check Date Format Input into Excel Column with VBA code

    Works perfectly now. Thanks again. You are a genius!

    Are you able help me with my other post : Check entry in Excel column if data is in the same row of a different column with VBA ?
    I can not believe that this can not be done except manually after pasting as suggested.

  15. #15
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Date Format Input into Excel Column with VBA code

    Hello stanlelma,

    You're welcome.

    No problem, I take a look right now.

  16. #16
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code

    Hello Leith Ross,
    Could you please help me to do previous same functionality for the date format "DD/MM/YYYY".

  17. #17
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code

    Hello Leith Ross,
    Could you please help me to do the previous same functionality for the date format "DD/MM/YYYY".

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Check Date Format Input into Excel Column with VBA code

    inoka welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  19. #19
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code

    How to write new one? I was unable to find the place. Actually, this thread is related to my issue. only one difference is my date format should be in "DD/MM/YYYY".

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Check Date Format Input into Excel Column with VBA code

    I understand it is related, but please read my comments about why we dont permit "hijacking" of other member's threads.

    To starts a new thread, click on the Forum button towards the top of the page, select the forum you want (VBA), click Post New Thread, add your question and then copy/paste the link from this thread

  21. #21
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code

    I added new thread. But, I didn't receive any reply.

    https://www.excelforum.com/excel-pro...ml#post4800575

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Check Date Format Input into Excel Column with VBA code

    Yes, actually you did

  23. #23
    Registered User
    Join Date
    01-18-2024
    Location
    South Africa
    MS-Off Ver
    365
    Posts
    2

    Re: Check Date Format Input into Excel Column with VBA code

    Quote Originally Posted by inoka View Post
    Hello Leith Ross,
    Could you please help me to do the previous same functionality for the date format "DD/MM/YYYY".
    Hi there

    7 years late on this one, but I needed to do the same thing. I've found a way to solve this, but it may not necessarily be completely bullet-proof.
    I've taken most of the code, but you'll see I do some specific checks. If the format is dd/mm/yyyy I expect, at least, that the minimum length is 8, and that there can be found exactly 2 instances of the "/" character. If at least that is true, then we're able to do the remaining checks in the code as written by Leith Ross.

    Please Login or Register  to view this content.
    Last edited by FDibbins; 01-20-2024 at 12:52 AM.

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Check Date Format Input into Excel Column with VBA code

    Thanks for the input

    For future reference, please use code tags when posting code (see my signature on how to). I have added them for you - this time

  25. #25
    Registered User
    Join Date
    01-18-2024
    Location
    South Africa
    MS-Off Ver
    365
    Posts
    2
    Quote Originally Posted by FDibbins View Post
    Thanks for the input

    For future reference, please use code tags when posting code (see my signature on how to). I have added them for you - this time
    Noted - thanks kindly.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1