bjnockle
02-12-2013, 06:53 PM
I am trying to modify a vba code written by s76.vj yesterday to automatically stamp "Time" in column C when column A a item is selected from the drop-down list in column A (this part is working great); I also want the code to automatically stamp "date, month and time" in column F, E and D respectively when an item is selected from the drop-down list in column G (Received By -title heading) - this is one part the code is not able to capture.
I believe that the code will need to be modified to capture the "date, month and time" in column F, E and D respectively when an item is selected from the drop-down list in column G.
I need assistance in modifying the code to stamp date, month, and time separately in column F, E and D respectively. [B]Below is the code:
Note: I have attached a sample copy of the template.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column <> 1 And .Column <> 6 Then Exit Sub
Application.EnableEvents = False
If .Column = 1 Then
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
ElseIf .Column = 6 Then
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
.Offset(0, -2).ClearContents
Else
With .Offset(0, -2)
.NumberFormat = "hh:mm:ss"
.Value = Now
End With
With .Offset(0, -1)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
End If
End If
Application.EnableEvents = True
End With
End Sub
I believe that the code will need to be modified to capture the "date, month and time" in column F, E and D respectively when an item is selected from the drop-down list in column G.
I need assistance in modifying the code to stamp date, month, and time separately in column F, E and D respectively. [B]Below is the code:
Note: I have attached a sample copy of the template.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column <> 1 And .Column <> 6 Then Exit Sub
Application.EnableEvents = False
If .Column = 1 Then
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
ElseIf .Column = 6 Then
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
.Offset(0, -2).ClearContents
Else
With .Offset(0, -2)
.NumberFormat = "hh:mm:ss"
.Value = Now
End With
With .Offset(0, -1)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
End If
End If
Application.EnableEvents = True
End With
End Sub