Solved: Time Stamp in cells using VBA

[bjnockle]

I am working on a template that is intended to "Time Stamp date and time - for example 11 Feb 2013 22:58:47" in column C when the returnee name (someone given the equipment to use) is selected from a drop-down list in column A. (Note: the code I have, thanks to McGimpsey & Associates, captures stamping the date and time section for column C)

I also want the vba code to Time Stamp (23:20:25) column D when a name is selected in the drop-down list in column F as well as stamp the date in E as Monday, 11 Feb 2013.

I need assistance in modifying the code to stamp time and date separately in column D and E respectively. Below is the code:

Note: I have attached a sample copy of the template - with the vba code.

[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A4:A10000"), .Cells) Is Nothing Then
Application.EnableEvents = False
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
Application.EnableEvents = True
End If
End With
End Sub[/VBA]

[Bob Phillips]

You could have removed the protection from the VBA to make it easier for us (as such, this is not tested at all)

[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo ws_exit

Application.EnableEvents = False

With Target

If .Count > 1 Then Exit Sub

If Not Intersect(Me.Range("A4:A10000"), .Cells) Is Nothing 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 Not Intersect(Me.Range("E4:E10000"), .Cells) Is Nothing Then

If IsEmpty(.Value) Then

.Offset(0, -2).Resize(, 2).ClearContents
Else

With .Offset(0, -2)

.NumberFormat = "hh:mm:ss"
.Value = Time
End With
With .Offset(0, -1)

.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub
[/VBA]

[snb]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)


With Target

If .Count > 1 Then Exit Sub
If .column=1 or .column=6 Then
Application.EnableEvents = False
if .column=1 then
.Offset(, 2)=iif(.value="","",format(now,"dd mmm yyyy hh:mm:ss"))
Else
.Offset(, -2).resize(,2)= split(format(now,"hh:mm:ss_dd mmm yyyy"),"_")
End If
Application.EnableEvents = True
End If
End With
End Sub

[/vba]

[bjnockle]

As suggested by xld, I am posting this for slight modification to the initial problem posted 2 days ago.

Date, Month, and Time Stamp using VBA
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 for your convenience.

[bjnockle]

xld: Thank you very much for the solution to the problem posted. As suggested in the other post for Date, Time and month Stamp using VBA, I am posting the contents here.

As suggested by xld, I am posting this for slight modification to the initial problem posted 2 days ago.

Date, Month, and Time Stamp using VBA
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 for your convenience.