Date Picker changing cell format

[Sileed]

Hi there,

Here's the issue I'm hoping someone can help with (I've pieced the solution together from some code I found here: http://www.contextures.com/xlDataVal14.html
That link explains how to insert a combo box in fields that are using data validation from a list (to take advantage of combobox feature over the default drop down in excel). I changed that code to insert MS Date Picker instead of a combobox if the data validation is set to Date

Here is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("DPick")
On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .LinkedCell = ""
    .Visible = False
    
  End With
End If

  On Error GoTo errHandler
  If Target.Validation.Type = 4 Then
    'if the cell contains a data validation date
    Application.EnableEvents = False
    
    Target.NumberFormat = "mm/dd/yyyy" 'format the cell to a date
    
    DPick.Value = Date 'default date picker to today if field is blank
    
    With cboTemp
      'show the Date Picker
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errHandler:
  Resume exitHandler

End Sub

I have 2 problems:

#1 If the field is blank, the date picker default to today (Great!) but if try to accept that value and move to a new cell the last cell remains blank
#2 once you choose a date value with the date picker, the cell shows a Data Validation error - it seems the value is a string (left justified) - selecting the cell, F2 then Enter corrects the issue.

Can anyone help with these issues please?