
Originally Posted by
browniiesx
The problem is, the people inserting those dates are speaking different languages, and so their Excel is programmed in a different one. A date like 13/02/2024 is not read as a date on our French Excel, only text. Thus the errors.
Just to say: I tried data validation, but since they copy/paste their information, it doesn't work.
Try this:
The code will check 2 things:
1. The cell must be formatted as "yyyy-mm-dd", If it is not, then the code will apply the format to the cell in this part:
2. The cell text must be Like "####-##-##", see this part: 'Not rngCell.Text Like "####-##-##"', notice that I'm using .Text instead of .Value.
Notes:
1. If a user inputs a date by typing in the cell, any date format is allowed, but it will be automatically converted to yyyy-mm-dd.
2. If a user inputs a date by copy-paste:- If it is pasted as a value, then any date format is allowed, but it will be automatically converted to yyyy-mm-dd.
- If it is pasted as All, then it will be rejected if the format isn't "yyyy-mm-dd".
Bookmarks