Jabberwokki
New Member
- Joined
- Dec 2, 2020
- Messages
- 33
- Office Version
- 2010
- Platform
- Windows
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
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