Error entering data to cell w/Dropdown & Data Validation

First off, I realize this posting may be a bit too much to ask. However, if some of you VBA gurus wish to challenged, please read on. It would be most appreciated.

I have a complex spreadsheet tracking an amortization schedule. For the most part everything is working fine. I just decided to make one small change. Since then I am having a problem with entering data into a cell with a dropdown. If the entry is chosen from the dropdown list all works fine. However, it does not accept a keyed-in entry. In many instances, the user will be keying in not selecting the entry from the dropdown.

In the attached spreadsheet, the dropdown is in cell K10. The problem arises when an entry is made in K9 (which is required prior to K10). If K9 is populated, then an expectable entry date can only be selected from the dropdown. I’d like the user to either select the appropriate date from the dropdown or be able to keyin an acceptable date.

As example (see attached):
Cell K9 has a Data Validation criteria set as a List (Range “=$P$33”$P$633”) with an “Error Alert” message. If an entry is made in K9 (say 1000), and the user selects Nov. 1, 2023 as the date from the dropdown list, all works great. However, if the user wishes to keyin 11-1-23, the Error Alert message is displayed. I’m confused as to why the error message is being triggered even though the entry is legitimate (i.e., same date as the dropdown).

Once an accepted Amount (K9) and accepted Date (K10) is enter, the “Additional Payment” in the Schedule below (column I) is updated.

Note, I am not a programmer so please excuse me for the sloppy coding. I have been piecing this together for some time. I’m sure it could be much cleaner and more efficient but it works.

Thanks for viewing,
Steve K.

Hi there,

I THINK that the problem is caused by Excel trying to validate the data value before trying to do anything else.

As a very simple example, select any value from the dropdown list. Then "edit" (i.e. without changing anything) that value by pressing F2 (to enter Edit mode) and then Return. The data validation error message will appear.

Either the value displayed in the Formula Bar (e.g. 09/01/2023) or its numeric equivalent (45170) is probably being compared with the allowable value (Sep 01, 2023) from the data validation list, and no match is detected.

The "ramifications" of formatted date values can give rise to all sorts of complications in Excel. If it were me, I would probably display a calendar whenever Cell K10 is selected, and choose the required date from that.

I hope this helps, but I am quite happy to be corrected by someone more knowledgeable


Regards,

Greg M

Thank you Greg for your response. I'm still working on this but I'm getting closer.
Again, much appreciated.

Hi again,

Many thanks for your feedback.

Let me know if you need any assistance regarding the use of a Calendar UserForm.

Regards,

Greg M