VBA code for timestamp i\'m currently working on a timestamp tool. i\'d like to

Question

VBA code for timestamp

i'm currently working on a timestamp tool. i'd like to ask what code can I use to do this. Basically, there is a validation list on column A where it says "Opened" and Fixed." in column B, there is the Start time and then the end time is on column C. so when I select "opened" column b should timestamp and then when i "fixed" timestamp will be on column C. i found a code online but i'm having a hard time configuring it or if it is applicable. Hope anyone can help me. Thanks a lot in advance :) - Anne

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

Thanks for help !!

Explanation / Answer

Hi ..

Try this code::

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Columns("A"), Target) Is Nothing Then Exit Sub

   Select Case UCase(Target)
      Case "": Target.Offset(0, 1).ClearContents: Target.Offset(0, 2).ClearContents
      Case "OPENED"
         With Target.Offset(0, 1)
            .NumberFormat = "dd mmm yyyy hh:mm:ss".
            .Value = Now
         End With
      Case "FIXED"
          With Target.Offset(0, 2)
            .NumberFormat = "dd mmm yyyy hh:mm:ss"
            .Value = Now
         End With
      Case Else
   End Select
End Sub

Open the VBE, right click the sheet you want to use, right click and paste the code.

Save the file in xlsm format.

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.