VBA enter today's date if anything is typed in cell

[bh24524]

Hello, I'm looking for event code that will automatically put the current date in column C whenever a value is entered into column A. It's just a simple sheet that looks like this:

I tried entering this piece of code into greater code:

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Target.Offset(, 2) = Date

I thought that might work, but it didn't. There are merged cells in this, I'm not sure if that matters. If it does, row 12's header isn't always in row 12 - I have to insert and delete rows in this all the time as I add or remove names. The first 3 rows are always the same. I don't know if it's the merged cells throwing this code off? Any help is appreciated.

 

[Joe4]

that what I thought because doesn't work for me from the beginning!
but I see exit sub is not useful here , because even if the cell is empty what I press inside the cell will populate date!
I suppose if the cell empty when click the cell then shouldn't populate date based on exit sub!
what I misunderstood?!

Are you familiar with in-line IF/THEN statements? They do not need and END IF if the action to do is on the same line as the IF.
That is what we are doing here.

Let's color code the lines of code and run through some examples:

    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Target.Offset(, 2) = Date

Example 1:
If a value in cell F4 updated, the intersection of the updated cell and column A would be nothing.
Therefore the Red part of the code would be invoked.
So it would "Exit the Sub", and the green line of code would NOT run.

Example 2:
If a value in cell A4 updated, the intersection of the updated cell and column A would be something.
Therefore the Red part of the code would NOT be invoked.
So it would go on and run the green line of code, and update cell C4.

Make sense?
It is even easier to see if you put a break point at the beginning of your code, and make a change, and then use the F8 to advance through your code one line at a time where you can see exactly what is happening, as it happens.

 

[Joe4]

So I inserted a row between 3 and 4. My actual sheet has names but I white-fonted them to screenshot here. I hadn't got to type anything because as soon as I inserted that row, I had gotten that error. maybe the error is triggering because data that is already in A is moving to new rows upon insert?

Every time you reply, you add another very important detail you failed to mention before!
Things like trying to add this code to existing code and inserting or deleting lines are very important to how this code works, as adding or removing rows will trigger the code to run.

In order to determine the best way of doing this, we need this question answered:
When updating column A or B manually, is it possible that they would EVER be updating multiple cells at a time (i.e. like with copy and paste)?
Or will they only be editing one cell at a time?

 

[abdelfattah]

Example 1:
If a value in cell F4 updated, the intersection of the updated cell and column A would be nothing.
Therefore the Red part of the code would be invoked.
So it would "Exit the Sub", and the green line of code would NOT run.

so you mean to doesn't populate DATE in this case?

 

[Joe4]

OK, here is a way that should handle the case where you may be inserting rows:

Private Sub Worksheet_Change(ByVal Target As Range)

'Exit if whole rows are inserted or deleted
If Target.CountLarge >= Columns.Count Then Exit Sub

'Column B update check
Dim c As Range, d As Range
Set d = Intersect(Range("B:B"), Target)
If Not d Is Nothing Then
    Application.EnableEvents = False
    For Each c In d
        If IsNumeric(c) And c <> "" Then
            Select Case c
                Case Is = 9
                    c = c & " LA"
                    c.Offset(, 2) = "T"
                Case Is < 10
                    c = c & " LA"
'                Case 10 To 29
'                    c = c & " Sample 3"
                Case Is >= 104
                    c = c & " hours"
                    c.Offset(, 2) = "T"
                Case 30 To 104
                    c = c & " hours"
            End Select
        End If
    Next
    Application.EnableEvents = True
End If

'Column A update check
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(, 2) = Date
    Application.EnableEvents = True
End If

End Sub

Just note that because of your previous error, your automation may be shut off. You can turn it on again by manually running this little procedure:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[Joe4]

so you mean to doesn't populate DATE in this case?

I am not sure how else to explain this to you so that you will understand.

The easiest thing is to run your own test and see for yourself.

Try using this code (from post 2):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Target.Offset(, 2) = Date
End Sub

Try updating ANY cell in column A, and you can see for yourself that column C in that same row gets updated.

Now try updating ANY cell in any other column (other than column A), and you will see that column C is NOT updated.

 

[abdelfattah]

Try updating ANY cell in column A, and you can see for yourself that column C in that same row gets updated.

I know so.

Now try updating ANY cell in any other column (other than column A), and you will see that column C is NOT updated.

so I misunderstood !
I thought this is just relating column A , not the others columns .
I thought when update empty cell for column A without write anything in empty cell then shouldn't populate date in column C .

 

[My Aswer Is This]

I do it this way:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
    Target.Offset(, 2).Value = Date
End If
End Sub

 

[Joe4]

I do it this way:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
    Target.Offset(, 2).Value = Date
End If
End Sub

Yep, that is the great thing about Excel -- there are often many different ways to accomplish the same thing.

One of the main reasons to use the Intersect method is if they may be updating more than one cell at a time (i.e. through copy/paste).
Their current code for the column B updates is a good example of how to handle that situation.

 

[bh24524]

Every time you reply, you add another very important detail you failed to mention before!
Things like trying to add this code to existing code and inserting or deleting lines are very important to how this code works, as adding or removing rows will trigger the code to run.

In order to determine the best way of doing this, we need this question answered:
When updating column A or B manually, is it possible that they would EVER be updating multiple cells at a time (i.e. like with copy and paste)?
Or will they only be editing one cell at a time?

But I did include that information in the original post:

As for your question in bold, There wouldn't be multi-cell updating, it would just be one at a time.

I used your updated code and that is working and doing what I requested, so thank you for that.

 

[Joe4]

You are welcome.
Glad we were able to help.

But I did include that information in the original post:

Sorry I missed/overlooked that! I apologize.