Hello,
How can I add a mask that would make faster the input of a year between 2000 and 2018.
I was thinking of something like this "20"00 resulting in something like that "20__" for the user.
The problem is that if I also use the data validation (between 2000 and 2018), Excel won't detect any mistakes.
I wonder if there is any excel built in solution. If not, I can use VBA. Any Ideas?
Thank you,
Why won't data validation detect a mistake if a number between 2000 & 2018 isn't entered? That's precisely what data validation does.
Or are you saying you want the user to enter a number between 0 and 18 and the cell to result in 2000....2018?
In which case a sheet change event procedure like the following. Change the A1 reference as necessary.
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Originally Posted by Richard Buttrey
Yes. I did accomplish by using a custom mask in the cell's property ("20"00), which worked, but the data validation parameter (between 2000 and 2018) was not detecting any mistakes.
Applying this code to the needed range will most likely work, thank you.
I wonder, however, why didn't the data validation worked?
If type "19", and he apply the filter, it would be a invalid parameter. If type "19", then he transform it into 2019, and then apply the filter, it would be invalid as well... But he didn't worked in any of those case...
Cheers
That is because the cell ONLY contains what you type in, and not (necessarily) what it shows. So if you type 20, thats all the cell contains - 20. the rest is just a visual display of how you want it shown.
Another example is if you format a cell to only snow 2 decimal places, and enter 1.23456789, it will only show 1.23, but will still contain that full number
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
Got it. Thank you.
Happy to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks