Automatic date in a cell, when the corresponding row is updated or changed

S

Sarahd1

New Member
Joined
Mar 15, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi everyone. I have been trying to figure out a VBA code that will automatically input the date which the corresponding row is updated. I need the code in column P, starting in row 4, and I already have code in the document for some other columns. The goal here is to be able to see the last date that each row was updated.
Can anyone please advise a code that will do this? The rows are a mixture of free text and some dependent drop down lists. I will post the code and a screenshot of the code, that I already have below, can you please advise where to put the additional code for the dates? I have never used two different codes in one document before. Thank you!! :)
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 4 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 6 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    Application.EnableEvents = True
    
End Sub
 

Attachments

  • Code in worksheet (sheet 1).PNG
    Code in worksheet (sheet 1).PNG
    198.4 KB · Views: 20

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
I'm not 100% clear on exactly what you want, but please try the following code & let me know if it's getting close?
(EDITED)

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        Select Case Target.Column
            Case Is = 2, 4, 5, 6
                Application.EnableEvents = False
                If Target.Validation.Type = 3 Then
                    Target.Offset(, 1) = ""
                    Range("P" & Target.Row).Value = Date
                End If
        End Select
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
Hi Kevin, Thanks your reply, I appreciate your help. I will try to explain again. I am new to VBA codes.
1. I need the dates in column in column P , further; I want the date to automatically insert, when any cells to the left in that corresponding row are updated. changed or a new row of data is added. I.e, If one day I go in and make edits to cells D5 and G55 - I want the date in cells P5 and P55 to update to the current date. Likewise, If I add a new row of data in row 73, I want the current date to appear in cell P73.
2. I already have a code in the same worksheet, for a different purpose - and I don't know how to add an additional code or where to put it. When I add the code you have given me, below this code; I get this error - 'compile error: ambiguous name detected: worksheet_change'.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 4 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 6 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    Application.EnableEvents = True
 
Upvote 0
Sarahd1 said:
Hi Kevin, Thanks your reply, I appreciate your help. I will try to explain again. I am new to VBA codes.
1. I need the dates in column in column P , further; I want the date to automatically insert, when any cells to the left in that corresponding row are updated. changed or a new row of data is added. I.e, If one day I go in and make edits to cells D5 and G55 - I want the date in cells P5 and P55 to update to the current date. Likewise, If I add a new row of data in row 73, I want the current date to appear in cell P73.
2. I already have a code in the same worksheet, for a different purpose - and I don't know how to add an additional code or where to put it. When I add the code you have given me, below this code; I get this error - 'compile error: ambiguous name detected: worksheet_change'.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 4 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 6 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    Application.EnableEvents = True
Click to expand...
Understood. Could you please post your existing code?
 
Upvote 0
kevin9999 said:
I'm not 100% clear on exactly what you want, but please try the following code & let me know if it's getting close?
(EDITED)

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        Select Case Target.Column
            Case Is = 2, 4, 5, 6
                Application.EnableEvents = False
                If Target.Validation.Type = 3 Then
                    Target.Offset(, 1) = ""
                    Range("P" & Target.Row).Value = Date
                End If
        End Select
        Application.EnableEvents = True
    End If
End Sub
Click to expand...
Sorry, Kevin I posted a general reply to this thread instead of replying to you, and I can't seem to delete or edit my comment (can you tell I am new here?). Please see my reply above. Thank you
 
Upvote 0
kevin9999 said:
Understood. Could you please post your existing code?
Click to expand...
This is my existing code (for a different purpose), and the code you gave me below it.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 4 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 6 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    Application.EnableEvents = True
    
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        Select Case Target.Column
            Case Is = 2, 4, 5, 6
                Application.EnableEvents = False
                If Target.Validation.Type = 3 Then
                    Target.Offset(, 1) = ""
                    Range("P" & Target.Row).Value = Date
                End If
        End Select
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
Sarahd1 said:
Sorry, Kevin I posted a general reply to this thread instead of replying to you, and I can't seem to delete or edit my comment (can you tell I am new here?). Please see my reply above. Thank you
Click to expand...
The code I supplied in post #2 is meant to replace entirely the code you already have. It does all the things you ask for in a test file. Try and replace your existing code with mine & see what happens when you make changes in your worksheet :)
 
Upvote 0
WOW!!!! That is brilliant. Thank you SO MUCH. I wish you could have heard my reaction when I just used this. Thank you Kevin!!!
 
Upvote 0
kevin9999 said:
The code I supplied in post #2 is meant to replace entirely the code you already have. It does all the things you ask for in a test file. Try and replace your existing code with mine & see what happens when you make changes in your worksheet :)
Click to expand...
Sorry one more thing ?
I need the code for column P (the dates) to work if any cell in the row before P is updated, not just the ones that were covered by the previous code. So I need the date to update if there's any changes to columns A through to O (15 columns). I was going to add the numbers for the columns that aren't working at the moment, but I didn't want to mess the code up. Currently the date only changes or updates if there's changes to columns B to F.

Thank you !
 
Upvote 0
Sarahd1 said:
Sorry one more thing ?
I need the code for column P (the dates) to work if any cell in the row before P is updated, not just the ones that were covered by the previous code. So I need the date to update if there's any changes to columns A through to O (15 columns). I was going to add the numbers for the columns that aren't working at the moment, but I didn't want to mess the code up. Currently the date only changes or updates if there's changes to columns B to F.

Thank you !
Click to expand...
And do you want it to work only if the cell changed has a Data Validation applicable to it, or any cell in the range A:O?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Use change event to change the format of a date
Replies
2
Views
170
still learning
S
B
  • Solved
Stop UDF/code from updating time when cell is edited
Replies
6
Views
336
Georgiboy
Georgiboy
W
  • Question
How to monitor cells in a column for changes, to execute macro on a row?
Replies
5
Views
97
mumps
mumps
W
  • Solved
Code for rows of cells returning reverse-order values.
Replies
5
Views
182
Worksong
W
B
  • Question
Insert Julian day in excel cell
Replies
1
Views
206
Joe4
Joe4

Forum statistics

Threads
1,215,429
Messages
6,124,840
Members
449,193
Latest member
MikeVol

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top