title: Force string variable to UK date format
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>This is the original piece of VBA.</p>
  
  <pre><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 &gt; 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 _
          &amp; ", " &amp; newVal
        End If
      End If
    End If
  End If
  
  exitHandler:
    Application.EnableEvents = True
  End Sub
  </code></pre>
  
  <p>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).</p>
  
  <p>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.  </p>
  
  <p>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):</p>
  
  <pre><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 &gt; 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 &gt; 1 Then 'amendment
      If oldVal = "" Then
        'do nothing
        Else
        If newVal = "" Then
        'do nothing
        Else
        Target.Value = oldVal _
          &amp; ", " &amp; newVal
        End If
      End If
    End If
  End If
  
  exitHandler:
    Application.EnableEvents = True
  End Sub
  </code></pre>
  
  <p>I'm using Excel 2007, but the spreadsheet needs to be compatible with 2003.</p>
  
  <p>Edit:  The way the data looks is in this question here: <a href="https://stackoverflow.com/questions/16299946/excel-2003-how-to-count-dates-in-row-including-multiple-dates-in-cell">Excel 2003 - How to count dates in row, including multiple dates in cell</a></p>
  
  <p>Edit: I should also mention that if I type 01/07/2013 into a cell twice, it comes out like this:</p>
  
  <p>07/01/2013, 01/07/2013</p>
  
  <p>It's like the first time the cell is populated it switches it to US date format, but anything after is ok.</p>