In my recent task, I was required to accept input from the user. One input value was the date of birth and it, of course, must be a date. What I was doing, is storing the input in a variant data type. This made the code error-free and the code was accepting any value user was giving. Below is the code:
Dim dob As Variant
dob = Application.InputBox("Enter Your Date of Birth")
This was, of course, an incorrect approach as the user can input any value that may not be a valid date.
What I want is to make my code detect any invalid date and ask the user to input a valid date and end the subroutine.
Here's the code that allows users to put only a valid date as an input.
Sub check_date()
Dim dob As Date
On Error GoTo Errorhandler
dob = Application.InputBox("Enter Your Date of Birth")
On Error GoTo 0
Debug.Print dob
Exit Sub
Errorhandler:
MsgBox "Please enter a valid date."
End Sub
When you run the above code, it will ask you to enter your date of birth. If you enter an invalid date, it will ask you to enter a valid date and end the procedure.
How does it work?
The first and most important thing we have done is to use the Date type variable to store the expected date. Now, if you try to store a value that is not a valid date, it will through an error of "type mismatch".
Next, we catch this error using the On Error Goto error handler.
On Error GoTo Errorhandler
dob = Application.InputBox("Enter Your Date of Birth")
On Error GoTo 0
Using the On Error we jump the control to the tag Errorhandler, which is just before the End Sub statement. So the user is asked to enter a valid date and the sub ends.
Errorhandler: MsgBox "Please enter a valid date." End Sub
If the user puts a valid date, then the control flows normal and the input is saved in dob variable. We have put the Exit sub statement so that the control does not go to errorhandler and ends the procedure right there. So put your all end statements before exit sub. And that's it.
So yeah guys, this how you restrict the user to enter a valid date only. Let me know if you have any doubts regarding VBA dates or any other Excel/VBA related query. Ask us in the comments section below.
Related Articles:
Insert Date Time Stamp with VBA | To enter the timestamp when a specific cell was filled using this VBA Code. If you enter any value in Column A, The adjacent cell in column B will fill up with the time of entry automatically.
Date & Time in VBA | Find all topics on Date and Time in VBA on this page.
How we can format date through VBA? | Learn how to format date using VBA in Excel. Let’s take an example to understand how and where we can format the cell in short date number.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
