How not to mess up when you’re on a date.
Free Microsoft Office Tutorials
I’ve decided to add a new feature to Online PC Learning.com. Each week I will be adding an article that shows how to solve a problem that you will face in real life applications. This will enable me to reference these articles as we develop more complex projects, making the projects a little bit smaller and easier to get through.
The first topic I’ve chosen is one that is often faced in project application development. We need to allow the user to add a date that we are going to filter or reference in our application. What happens all the time is that they may not add a correct date and application fails or give incorrect data. So how do we restrict cells to a date format.
How not to mess up when you’re on a date and restrict cells to a date format
Download the template from here.
Download the free template to get you started. Note: This is not the completed project it is a template to help with the project.
Watch this video for an overview of “restrict cells to a date format”
Here are three easy methods to remedy that
Use data validation in the cell range to allow only a date to be entered.
Choose the Data tab and then select Data Validation/Allow/Date/Between/Enter Starting Date/Enter Finishing Date.
While the dialogue box is open add an import message and an error message. Make sure that your error message is set to Stop.
Look at the illustration below for the details.
The second method is to add some VBA code to the sheet event that will stop anything but a date being entered into a range or cell.
Here is the code. Put this into the sheet.You will notice here that I have used Worksheet Change event. You could also use the Worksheet Selection Change event.
Private Sub Worksheet_Change(ByVal Target As Range)
Set x = ActiveSheet.Range("N13:N37")
For Each c In x
If c.Value <> "" And Not IsDate(c) Then
MsgBox "Only a date between 1/1/2000 and 1/1/2030 is permitted in this cell."
Add a module and add the data validation with code. The reason why you do this is because users delete data validation when they cut and paste and in many other ways. This is a very easy method that puts at validation back in every time the workbook is open.
Put this code in a module.
Sheet1.Range("V13:V37").Validation.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1/1/2000", Formula2:="1/1/2030"
Now go to ThisWorkbook and add this code to the Open event.
Private Sub Workbook_Open()
There are the three methods to restrict cells to a date format. If at all possible you can get away with just adding data validation to stop people messing up your dates that I would suggest you do so. However if you find that users need to be allowed to copy and paste it may be better to reset your validation with code.