asked on

Capturing Date and Time Stamp

Hi I found this code online. So far this code works fine. When I enter a value in column A, the date is recorded in the adjacent column. Cool! But, I do not understand this code and would like to add comments to each line so I understand how it works. Could someone clearly dissect this code for me? Also, if I were to make the macro more robust by capturing the date and time when a particular target value is entered into column A ,.. for example [If there is a drop down list of restricted values "Yes" and "No"], I would want to capture the date and time when the user chooses "yes", and only the first instance of when this event occurs. how would that code look? Please include comments. Much Appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A2:A100")) Is Nothing Then

With Target(1, 2)

.Value = Date

.EntireColumn.AutoFit

End With

End If

End Sub

Phil Davidson

Private Sub Worksheet_Change(ByVal Target As Range) 

'a subroutine not accessible in other places of the code
'A parameter Target is taken in when this function (aka subroutine) is called.  ByVal signifies that the Target cannot
' be changed herein.

If Target.Cells.Count > 1 Then Exit Sub   'conditional logic to test if the Count in the Cells value of Target is greater than one.  If it is, this subroutine will exit.  This makes me think part of the original poster's request won't be possible.

        If Not Intersect(Target, Range("A2:A100")) Is Nothing Then  'if the target cell and a value in the range of cells of column A (from row 2 through 100) is the same, the following will be executed.

            With Target(1, 2)  'Taking the target and two positional integers for the execution below

                .Value = Date     'a column gets assigned a date

                .EntireColumn.AutoFit    'the column is fitted for the size of the cell

            End With

        End If

End Sub

Open in new window

For more about the With clause, see this link: http://www.homeandlearn.org/with_end_with.html

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

Regarding your desired changes, it works like this: You check if the B column already has a value. Only if not, then check if the current cell's value is "yes", and if so set the B value to the current date.

' * Private Sub = A procedure which cannot be seen outside of the modul part it is in
' * Worksheet_Change is the default event handler called if a cell of this worksheet changes
' * Target is a parameter, and ByVal means the Sub cannot send changes to it back to the caller
Private Sub Worksheet_Change(ByVal Target As Range)
    ' * more than one cell changed => leave sub, do nothing
    If Target.Cells.Count > 1 Then Exit Sub
    ' * If the target cell is NOT in the range of cells A2 to A100 (column A, rows 2 to 100) then leave sub 
    If Intersect(Target, Range("A2:A100")) Is Nothing Then Exit Sub
    ' * If the B cell contains a value, leave sub
    If Target(1,2) <> 0 Then Exit Sub
    ' * if current cell contains "yes", set B cell to current date, and fit B column to values
    If Target.Value = "yes" then
         Target(1,2).Value = Date
         Target(1,2).EntireColumn.AutoFit 
    End If
End Sub

Open in new window

Phil Davidson

I want to correct something in my previous post.

The comments for this code that I wrote were wrong:

If Not Intersect(Target, Range("A2:A100")) Is Nothing Then

Open in new window

Please ignore this sentence from my comments:
'if the target cell and a value in the range of cells of column A (from row 2 through 100) is the same, the following will be executed.'

I think Qlemo has a better response on this one.

Professor J

well explained Qlemo