VBA Date stamp from list validation dropdown

[Jabberwokki]

Hi there

I'm using the following code to auto populate dates in various cells based on a selection from a list validation drop down box. For some reason the VBA is also adding Hours, Minutes and Seconds to the resulting value. I can't seem to get just a date regardless of the .NumberFormat = "dd/mm/yyy"

Any advice please?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cols As Long

' Auto populate status dates for 7 column setup

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("N2:N1000")) Is Nothing Then
With Target
If .Value = "" Then Exit Sub
Select Case .Value
Case "backlog": Cols = 1
Case "ip": Cols = 2
Case "blocked": Cols = 4
Case "unblocked": Cols = 5
Case "comp": Cols = 6
Case "nr"
Cols = 1
If .Offset(0, 2).Value = "" Then
.Offset(0, 2).NumberFormat = "dd/mm/yyyy"
.Offset(0, 2).Locked = True
.Offset(0, 2).Value = Now
End If
If .Offset(0, 6).Value = "" Then
.Offset(0, 6).NumberFormat = "dd/mm/yyyy"
.Offset(0, 6).Locked = True
.Offset(0, 6).Value = Now
End If
End Select
If .Offset(0, Cols).Value = "" Then
.Offset(0, Cols).NumberFormat = "dd/mm/yyyy"
.Offset(0, Cols).Locked = True
.Offset(0, Cols).Value = Now
End If
End With
End If
End Sub

Many thanks in advance

Regards

Dominic

 

[mumps]

Try replacing "Now" with "Date".

 

[Jabberwokki]

Hi Mumps

You are a SUPERSTAR! Such a simple fix....Thank you

 

[mumps]

You are very welcome.