+ Reply to Thread
Results 1 to 4 of 4

Excel app won't accept 2020 dates on one tab, but will on other tabs

  1. #1
    Registered User
    Join Date
    02-01-2020
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Excel app won't accept 2020 dates on one tab, but will on other tabs

    EDIT: I think the problem is resolved (see post #3) - thanks, Greg!

    PREVIOUS EDIT: I just attached a screenshot of the date-error message I get; probably won't help with debugging, but you can see it supposedly allows 2020 dates, but errors out on them.

    Hi all,

    Just joined the forum, hoping to get a nudge in the right debugging direction to fix a problem I can't solve on my own. My knowledge of VB and Excel macros is beginner-level at best, but I'm usually pretty good at figuring out existing code for languages I don't know. This time, I'm stumped.

    I'm running an early-2000s Excel application to manage multiple stock-trading accounts; the app hasn't been updated in years and the author seems to have gone dark, but the app itself still functions running on Excel 2007 and is essential to my daily trade-management activities.

    The problem is that one module will not accept date entries for 2020, but other portions of that same module will accept them, as well as other worksheets in the application. Everything worked fine through the end of 2019 and the problem didn't reveal itself until I tried entering a trading expense for January into the app. It looks to me that some sort of date-validation code has barfed once the current year hit 2020.

    I have opened the sheets in Visual Basic developer mode and done searches for date ranges, "2019", "2020", "20" and everything else I could think of, looking for a date-range test and just can't find anything, but I'm sure this is due to lack of knowledge of Excel programming. I would greatly appreciate some guidance into what needs patching to eliminate this bug.


    Many, many thanks to anybody who might be able to help me out.

    Best Regards,
    Jim
    Attached Images Attached Images
    Last edited by JimPT; 02-02-2020 at 12:28 AM. Reason: Added error screenshot

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Excel app won't accept 2020 dates on one tab, but will on other tabs

    Hi there,

    Cell N7 of the "Expenses" worksheet in the "TOV4-120 Acct01 Debug testing 1.xls" workbook has "native" Excel data validation (i.e. not VBA data validation) set to accept dates in the range 1/1/1900 to 1/1/2020. Setting this range to more appropriate values will allow dates for the year 2020 to be inserted.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    02-01-2020
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Excel app won't accept 2020 dates on one tab, but will on other tabs

    EDIT: Greg, I think I found the answer, by following up on your reply and doing a bit of searching.
    This URL held the answer, so I tested one cell and it worked once I opened up the date range.

    support.office.com/en-us/article/Apply-data-validation-to-cells-29FECBCC-D1B9-42C1-9D76-EFF3CE5F7249

    I notice that the master worksheet, with a similar Expenses tab, had no such restrictions on data validation - that's why it never had an issue with a 2020 date.
    Will fix all the sheets tomorrow with a clearer head, but I think I've got the problem licked thanks to the trail you pointed out. Many thanks!

    I'm a little uncomfortable being here only to get help without being able to offer much in return; Excel programming is not my expertise, so I'll put this on the Debit side of my karma ledger. It's moot here, but I'm able to help on other forums for technology, cooking and pizza-making, so I'll endeavor to put a few more Credits in that column elsewhere and hope it all evens out in the end.

    --------------------------

    Quote Originally Posted by Greg M View Post
    Cell N7 of the "Expenses" worksheet in the "TOV4-120 Acct01 Debug testing 1.xls" workbook has "native" Excel data validation (i.e. not VBA data validation) set to accept dates in the range 1/1/1900 to 1/1/2020. Setting this range to more appropriate values will allow dates for the year 2020 to be inserted.
    Thank you, Greg. I think I get the gist of what you're saying - that there's more than one type of inherent date validation, and that the cell is currently governed by a default Excel setting rather than being overridden by VBA coding? Since it's Excel 2007, I can see Microsoft, at the time, picking 2020 as an arbitrary future date when it wouldn't be in much use anymore. Perhaps I'm the last user left on Earth.

    Since it works OK in the top-level workbook (the "Gov" module that's opened by Windows first), maybe I just need to find, then cut/paste VBA code that accomplishes the change; I'll try poking around to see if I can find it. Or is it a global setting for the sheet, accessed through the menus, rather than in the code?

    I'll poke around with it, need to look up how to handle this validation, but would you mind giving me a pointer where to look for that? A right-click on the cell didn't give me any options, but perhaps I neglected to put it into a "programming" rather than "user" mode.

    Is it possible to set this validation globally, or is it done on a cell/row/column basis?

    Your reply gave me a solid lead, that it's a validation-setting issue, but perhaps one that's not in the author's coding but rather in Excel settings?

    And, it probably doesn't matter to resolving the problem, but would you know why the error message appears to allow dates through 2020, yet rejects them anyway? Is that just a typo in whatever text string was entered in the validation setting?

    Cheers and thanks,
    Jim
    Last edited by JimPT; 02-12-2020 at 03:40 PM. Reason: Found out how to do data validation; problem solved, I think.

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Excel app won't accept 2020 dates on one tab, but will on other tabs

    Hi again,

    Apologies for the delay in replying.

    Many thanks for your feedback and also for the Reputation increase - much appreciated!

    You're welcome - glad I was able to help.

    Best regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Call in the Cavalry - 2020
    By JBeaucaire in forum The Water Cooler
    Replies: 97
    Last Post: 12-15-2020, 04:27 PM
  2. [SOLVED] Why does a new installation of Excel 2003 refuse to accept dates?
    By RogeratCCCC in forum Excel General
    Replies: 3
    Last Post: 12-22-2012, 10:56 AM
  3. Should I use calendar control to accept dates from user?
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-13-2012, 03:20 AM
  4. Force a column to accept only dates
    By mosaictu in forum Excel General
    Replies: 4
    Last Post: 08-04-2011, 02:39 AM
  5. [SOLVED] format combobox in userform to accept dates
    By Brad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-02-2005, 06:20 PM
  6. format combobox in userform to accept dates
    By Brad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-02-2005, 06:17 PM

Tags for this Thread

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