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]

Excel has a built in shortcut for that if you werent aware. Press CTRL then ;

 

[ipbr21054]

I did see that would like to apply code for it

 

[Rick Rothstein]

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
        [B][COLOR="#FF0000"]Target.NumberFormat = "dd/mm/yyyy"[/COLOR][/B]
    End If
End Sub

Could you advise please.
Thanks

Try adding the code line I show in red above and see if that makes the code do what you want.

 

[DanteAmor]

How about

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
[COLOR=#0000ff]        Target.NumberFormat = "dd/mm/yyyy"[/COLOR]
    End If
End Sub

 

[ipbr21054]

OK
At first it made no difference but then i remember that column is formatted as Text.

Now that i have formatted that column to Date it works.

So i will need to keep an eye on it to see / remember why it was formatted as Text .

many thanks

 

[DanteAmor]

You're welcome

 

[ipbr21054]

Morning,
Following on from above.

The current code in use is supplied below.
This months new worksheet in column A is formatted as DATE

I can double click in any cell in column A and as opposed to me now seeing 01/04/2019 i actually see 04/01/2019

This is what i was talking about in post #6 above

So as a test in the code below i changeds this dd/mm/yyyy to mm/dd/yyyy and now i see 01/04/2019

If i change it back to dd/mm/yyyy then i see 04/01/2019

** Even if i manually type 01/04/2019 it then automatically changes itself to 04/01/2019 **

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 = "dd/mm/yyyy"
    End If
End Sub

 

[steve the fish]

If you format the date to general then you should see 43556 on the 1st of April 2019. If you dont try using CDate(Date).

 

[ipbr21054]

I didnt see 43556

Regarding you advise on CDate(Date) is the below correct as i still see it entered as 04/01/2019 when dd/mm/yyyy is in the code.
What should column A be formatted as or dont that make any difference ?

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 = CDate(Date)
        Target.NumberFormat = "dd/mm/yyyy"
    End If
End Sub