Change date format in cell when using excel date addin

[nando88]

I found this excel addin, and I need to change the date format and locale, but when I use numberformat in the cell, the date format doesn't change:
Excel date addin

This si the code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Selection.NumberFormat = "[$-es-HN]dddd, dd"" de ""mmmm"" de ""yyyy;@\]"
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call CallDatePickerWithVBA
End If
End Sub
Sub CallDatePickerWithVBA()
    Dim TestWkbk As Workbook
    Dim obj As Object

    If Val(Application.Version) >= 12 Then
        Set TestWkbk = Nothing
        On Error Resume Next
        Set TestWkbk = Workbooks("WinDatePicker.xlam")
        On Error GoTo 0
        If TestWkbk Is Nothing Then
            MsgBox "Sorry the Date Picker add-in is not open."
        Else
            Application.Run "'" & TestWkbk.Name & "'!OpenDatePicker", obj
        End If
    End If
End Sub

 

[Flaiban]

Try this code to Worksheet_Change event :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$A$1" Then
Selection.NumberFormat = "dd \d\e mmmm \d\e yyyy;@\]"
End If

End Sub

 

[nando88]

Still no luck. I think I figured out how to make it work, but you can't change the format on the same cell with vba. I tried this several times and noticed that the format does change but the addin switches back to the original format selected in the app, so the only way I figured out how to make this work is by addin the date in another cell and selecting the right format in the desired cell.
If you or anyone else finds a simpler way to do this through VBA, I will appreciate it.
Thanks for your help.