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
ioechoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phil DavidsonCommented:
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
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That's incorrect in many ways.
' * 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 in the range of cells A2 to A100 (column A, rows 2 to 100) then 
    If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
            ' * Use the following object whenever a command starts with a dot
            ' * Target(1,2) is the same row (=1), next column (=2) cell. It Target is A2, the result is B2
            With Target(1, 2)
                ' * change cell value with the current date
                .Value = Date
                ' * make the column width fitting to the values contained.
                .EntireColumn.AutoFit
            End With
        End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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

0
Phil DavidsonCommented:
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.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
well explained Qlemo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.