View Full Version : Formating date delimiters in real time

01-23-2016, 06:04 AM
I have already formated a column to Date and wish users to enter the date using the forward slash delimiters, however, users are sometimes
entering the date using the dot(full stop) as a delimiter.
I would like to force the dot to a forward slash as it is entered. However, if this involves the use of API I am NOT familiar with this use of it.
As an alternative to the above the dot delimiter can be changed to a forward slash as the user exits the cell for the next cell if this makes the coding easier.
It should be noted that some users use a leading zero for single digits and some do not that is why I thought it would be better to concentrate on the date delimiters.
ie 01.01.2016 to 01/01/2016 or 1.1.2016 to 01/01/2016

Finally, as if I havent made my request hard enough, I would like to be able to hide this code from the user.

Any help on some VBA code to do the above would be greatly appreciated.


01-23-2016, 04:23 PM
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns("DateColumn"), Target) Is Nothing Then FormatDate Target
'Edit "DateColumn" To the Column Number or use _
"Intersect(Range("X:X"), Target)" _
where "X:X" is the Column Address

End Sub

Sub FormatDate(Target As Range)
If Target.Count > 1 Or Not IsDate(Target) Then Exit Sub
Target = Format(Target, "ss/mm/yyyy")
End Sub