Force string variable to UK date format

Question

I've a piece of VBA code that is available via www.contextures.com. It enables users to add multiple entries to a data validation list seperated by a comma. The user selects from a list of dates, and a list of text entries.

The user can free type a date, or use the pick list to select a date or text entry. Sometimes two dates may need to go into a cell, so the VBA below allows the user to select/type one date, hit enter, then type another date in the same cell. As soon as the user hits enter, the previous date appears in the cell, with the new date following separated by a comma.

This is the original piece of VBA.

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 3 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      Target.Value = oldVal _
        & ", " & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

The problem is, when I enter a date in UK format into the cells the VBA applies to (such as 01/06/2013 i.e. dd/mm/yyyy), the VBA is intervening and returning the date as 06/01/2013 (mm/dd/yyyy).

My control panel settings are all UK format, so I figure it's something to do with how the VBA stores and then returns the date. I tried making amendments to the dims oldval and newval (to dates) but then the VBA doesn't allow multiple entries in one cell as before.

I amended the VBA to a hacky workaround (below) which formats the dates to the non standard dd.mm.yyyy (dd/mm/yyyy doesn't work) but this would require me to tell users to input dates in this format which I'd rather not do (I'd rather allow the standard 01/01/2013 or 01-01-2013 both of which are recognised as dates by Excel):

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Format(Target.Value, "dd.mm.yyyy") 'amendment
  Application.Undo
  oldVal = Format(Target.Value, "dd.mm.yyyy") 'amendment
  Target.Value = newVal
  If Target.Column > 1 Then 'amendment
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      Target.Value = oldVal _
        & ", " & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

I'm using Excel 2007, but the spreadsheet needs to be compatible with 2003.

Edit: The way the data looks is in this question here: Excel 2003 - How to count dates in row, including multiple dates in cell

Edit: I should also mention that if I type 01/07/2013 into a cell twice, it comes out like this:

07/01/2013, 01/07/2013

It's like the first time the cell is populated it switches it to US date format, but anything after is ok.

Answer 1

Okay, so after reading this article here: http://allenbrowne.com/ser-36.html I made some tweaks to the code:

 Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  'newVal = Target.Value 'amendment
  newVal = Format(Target.Value, "\ dd\/mm\/yyyy\")

  Application.Undo
  'oldVal = Target.Value 'amendment
  oldVal = Format(Target.Value, "\ dd\/mm\/yyyy\")
  Target.Value = newVal
  If Target.Column > 1 Then 'amendment
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      Target.Value = oldVal _
        & ", " & newVal
      End If
    End If
  End If
End If

exitHandler: Application.EnableEvents = True End Sub

The important bit appears to be the format:

oldVal = Format(Target.Value, "\ dd\/mm\/yyyy\")

Allen's suggested code was:

Format$(varDate, "\#mm\/dd\/yyyy\#")

I changed it to dd/mm and replaced the # with a space, and hey presto - it appears to be working! A very frustrating issue though, which I'll now have to make sure works on other users machines! Thanks to @sous2817 and @GSerg for their comments/suggestions.