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]

In order for this to work properly, this MUST be placed in a Worksheet_Change event procedure, named like this (you CANNOT change the name!)

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

And it MUST be placed in the Sheet module of the sheet you want to run it against.
The easiest way of getting there is to go to the sheet you want to apply it against, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code into the VB Editor window that pops up.

If you put in anywhere else, it will NOT run automatically.

 

[abdelfattah]

@bh24524
should be like this

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

you missed NOT is in bold , also should remove EXIT SUB(meaning the code doesn't do anything).
and cells should not be merged ,possibility shows error.

 

[Joe4]

@bh24524
should be like this

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

Nope, not necessarily. It can be done either way.
The original says:
"If the update is NOT in column A, exit the Sub"
So if the update is NOT in column A, then it will exit the Sub and never get to the update line.
If it is in column A, it will not exit the Sub and run the update line.

Your's says if the update IS in column A, then make the update.

The are both accomplishing the exact same thing and equally valid, as long as this is the only thing they are checking.

 

[bh24524]

Hi Joe, that is actually where I placed it, on the sheet. I do have another separate event procedure running in that same sheet, but as far as I know that should still work, because I have multiple event procedures in another sheet I use and they all run fine. When I insert a new row, I am getting:

Just in case, here is the rest of the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Range("B:B"), Target)
If d Is Nothing Then Exit Sub
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

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

End Sub

Could there be something in the portion of code above it that is causing the error?

 

[Joe4]

You are never getting there because of these lines near the top of your code:

Set d = Intersect(Range("B:B"), Target)
If d Is Nothing Then Exit Sub

An update to column A would result in the code exiting there, because that section of code exits if the update is NOT to column B!

If you have two different watched ranges in your procedure, you have to use the logic that abdelfattah recommended, and check to see if the change IS found in that range (act on the "positive" instead of the "negative").

Note that you were also getting an error because you were missing an "End If" line in your code at the end of your first block/check.

So your code would need to look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'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

 

[abdelfattah]

If it is in column A, it will not exit the Sub and run the update line.

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?!

 

[bh24524]

Okay, I think I understand. Although I pasted your code over mine and I am still getting an error:

This again happens just from inserting a row. It highlights the Target.offset(,2) = date line when I hit debug

 

[Joe4]

Exactly what cell are you updating when you get that error message?
What are you putting in that cell?
Is column C of that cell part of a merge cell or is it read-only?

 

[bh24524]

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?