Format question for double click = enter date to active cell

[ipbr21054]

Afternoon.
As per title.
When i double click a cell within the range mentioned in my code it should enter todays date.

The code below works fine apart from how the format date is entered.
So today its entered as 3/25/2019 but i would like to to be formatted & entered as 25/03/

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    If Not Intersect(Target, Range("A5:A28")) Is Nothing Then
        Cancel = True
        Target.Formula = Date
    End If
End Sub

Could you advise please.
Thanks

 

[steve the fish]

If you do what i said originally say in cell B1

CTRL then ;

What do you see?

 

[ipbr21054]

If the column is formatted as TEXT then i do CTRL then ; i see 01/04/2019

If the column is formatted as DATE then i do CTRL then ; i see 01/04/2019 BUT when i leave that cell it changes on its own to 04/01/2019

 

[steve the fish]

Do it in cell B1 so it is unaffected by the macro. What im not understanding is why this code is inserting a date that isnt the date of the system. You say the inserted date is not 43556 so im a little lost as to why. What is the number if you format to general?

 

[ipbr21054]

Format the cell to general then Ctrl ; then i see 04/01/2019

 

[steve the fish]

Then your system settings use MM/DD/YYYY? Yes? If you format that cell to number what does it say?

 

[ipbr21054]

System settings as in pc or something in excel ?

If i format that cell to NUMBER then CTRL ; i see 01/04/2019 BUT when i leave the cell i see 43556.00

 

[ipbr21054]

I have checked system settings and confirm that it is set to dd/mm/yyyy

 

[ipbr21054]

OK
Had some free time so i did this.

Start a new worksheet.
right click,view code then paste the below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    If Not Intersect(Target, Range("A4:A28")) Is Nothing Then
        Cancel = True
        Target.Formula = Date
        Target.NumberFormat = "mm/dd/yyyy"
    End If
End Sub

The date was entered as 01/04/2019 every time i made a double click.

I then added this to the code.

Private Sub Worksheet_Change(ByVal Target As Range)  If Not Intersect(Target, Range("E4:E30")) Is Nothing Then
    Application.EnableEvents = False
    Range("E4:E30").Formula = "=IF(C4="""","""",C4+D4)"
    Application.EnableEvents = True
  End If
  
'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub


'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 3 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "C") = Abs(Cells(Target.Row, "C")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "C").ClearContents
        End If
        Application.EnableEvents = True
    End If
    If Not (Application.Intersect(Target, Range("A3:G28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If


End Sub

I made the double click a few times & each time i see it like this 04/01/2019

I then deleted that code & made a few double clicks of which they all were entered as 01/04/2019

So without changing anything else do you see that the issue is with this shown below ?

Private Sub Worksheet_Change(ByVal Target As Range)  If Not Intersect(Target, Range("E4:E30")) Is Nothing Then
    Application.EnableEvents = False
    Range("E4:E30").Formula = "=IF(C4="""","""",C4+D4)"
    Application.EnableEvents = True
  End If
  
'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub


'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 3 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "C") = Abs(Cells(Target.Row, "C")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "C").ClearContents
        End If
        Application.EnableEvents = True
    End If
    If Not (Application.Intersect(Target, Range("A3:G28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If


End Sub