Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatic Updating Timestamp Using VBA

0

Hi,

I have created a VBA that automatically updates a timestamp in a cell when any cell its corresponding row is changed manually.

However it doesnt work if the value is changed through a formula, So for example if I call my sheet with timestamp and VBA sheet 1 and I have another sheet with data called sheet 2.

If data for cells in sheet 1 is calculated based on data it is looking up in sheet 2 and I change data in sheet 2, my timestamp in sheet 1 doesnt change even though the data within the cell values is changing.

Any help is appreciated,

Answer
Discuss

Discussion

Hello Pitty and welcome.

What you describe sounds quite possible using a worksheet event macro - probably Worksheet_Change event. If you could update your post by uploading a sample file (with no personal info) so we can see exactly what you are looking to achieve, we can better provide a solution.

Cheers  :-)
WillieD24 (rep: 537) Feb 27, '23 at 12:53 am
Hi Pitty

Willie is right - I can see that a Worksheet_Change event could be used to create a timestamp when a cell used in a formula changes (though it's a little more involved than you might think). Kindly edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. Then we should be able to give specific help.
John_Ru (rep: 6092) Feb 27, '23 at 4:02 pm
@John: (once again we were responding about the same time LOL)
Pitty hasn't provided a sample file but I took a stab at it anyway. My file has a Worksheet_Change event for both sheet1 and sheet2.
Sheet1 code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
' macro written by WillieD24 for teachexcel.com
' sample code to input date/time stamp on sheet1 in cell "G2"
 
If Target.Count > 1 Then Exit Sub
 
' limits event to column "E"
If Intersect(Range("E:E"), Target) Is Nothing Then Exit Sub
 
Application.EnableEvents = False
 
Target.Offset(, 2) = Now   ' 2 columns to the right of target cell
 
' when the cell is cleared, so is the date/time stamp
If Target = "" Then Target.Offset(, 2) = ""
 
Application.EnableEvents = True
 

End Sub

Sheet2 code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
' macro written by WillieD24 for teachexcel.com
' this code will put the value of sheet2("E2") into sheet1("E2")
' this causes the sheet1 "Worksheet_Change" event to run
 
If Target.Address = "$E$2" Then _
    Sheets("Sheet1").Range("E2").Value = Sheets("Sheet2").Range("E2").Value
 
End Sub

If Pitty decides to provide a sample file the code could be changed to be more specific.
Cheers   :-)
WillieD24 (rep: 537) Feb 27, '23 at 4:50 pm
@Willie another coincidence! I have an alternative (possibly more generic and informative) solution.  I was going to wait to see if Pitty provides a file (to save revising code and explanation) but decided to provide a demo Answer, as you have done.
John_Ru (rep: 6092) Feb 27, '23 at 5:24 pm
@Willie Perhaps a further coincidence- I think we previously both answered a question only to find the user was unresponsive. Hopefully not... 

@Pitty Please review and respond to the Answers provided below 
John_Ru (rep: 6092) Mar 1, '23 at 2:34 am
Add to Discussion

Answers

0

Hello Pitty,

Update:
I played around with this a bit and came up with this for you to consider.

The data displayed on sheet1 is the result of the in-cell formula - the formula doesn't change so the "Worksheet_Change" event is not triggered; and as a result the timestamp doesn't change. The timestamp on sheet1 will change if the formula is placed in the Worksheet_Change event code.

I made a simple workbook (attached) to demonstrate how this works. 
If you update your post to include a sample file we can provide a more specific answer.

Discuss
0

Pitty

It is possible to timestamp for a change in formula value by detecting a change in the data sheet (using the Worksheet_Change event) and tracing dependent cells then timestamping for those rows. This method works for all cells whose formula includes the changed cell and doesn't need you to map cells from one sheet to another. 

In the attached demo file, I've done that so you can:

  • Change a value in Sheet1 A2:B8 and the timestamps/ causes* will be recorded. 
  • Change a value in Sheet2 D2:D12 then activate Sheet1 to see the timestamps for formula value changes/ causes.

* I added a cell in column E which shows what caused (only) the last two changes of timestamp

How does it work? Sheet1 has this event macro, based on Don's tutorial Automatically Timestamp Entries in Excel but with settings /changes in bold below:

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("A2:B8")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("C" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("D" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

'### State last two causes of timestamps
With myUpdatedRange.Offset(0, 1)
    .Value = Cells(1, Target.Column).Value & " changed; " & .Text
    If InStrRev(.Text, ";") > InStr(.Text, ";") Then
        .Value = Left(.Text, InStr(InStr(.Text, ";") + 2, .Text, ";"))
    End If
End With

'Turn events back on
Application.EnableEvents = True

End Sub

If you change something in A2:B8 of Sheet1, a timestamp will appear in C or D and column E might get a cause like "Cost formula changed" (but it will show only the last two causes).

This doesn't work for formulas (dependent on value changes on Sheet2) so that sheet has the following Worksheet_Change event  (working for just the first range in bold and with comments for guidance): 

Public Evnt As String

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SelRange As Range, ArrowNum As Long

' expect arrow navigation to fail
On Error GoTo Done

With Target
    ' do nothing if more thatn on cell or out of range
    If .Count > 1 Or Intersect(Target, Range("D2:D12")) Is Nothing Then Exit Sub

    Set SelRange = Selection
    .Select
    Evnt = .Offset(0, -1).Text

    Application.ScreenUpdating = False
        .Parent.ClearArrows
        .ShowDependents

        'loop though any dependent cells on Target
        ArrowNum = 1
        'see if selection moves to any dependents
        If .Address(External:=True) = .NavigateArrow(False, 1, ArrowNum).Address(External:=True) Then GoTo Done
                'otherwise../
                Do
                     'timestamp if on Sheet1
                    If ActiveSheet.Name = "Sheet1" Then Call TimeStampDependents(Selection, Evnt)
                    'try another arrow
                    ArrowNum = ArrowNum + 1
                    .NavigateArrow False, 1, ArrowNum
                ' stop if that arrow doesn't exist
                Loop Until Err.Number <> 0
Done:
    .Parent.ClearArrows
    'return to original selection
    .Parent.Activate
    SelRange.Select
End With


End Sub

This finds which cells change as a result of them altering and the bold line triggers another event macro, as below:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SelRange As Range, ArrowNum As Long

' expect arrow navigation to fail
On Error GoTo Done

With Target
    ' do nothing if more thatn on cell or out of range
    If .Count > 1 Or Intersect(Target, Range("D2:D12")) Is Nothing Then Exit Sub

    Set SelRange = Selection
    .Select
    Evnt = .Offset(0, -1).Text

    Application.ScreenUpdating = False
        .Parent.ClearArrows
        .ShowDependents

        'loop though any dependent cells on Target
        ArrowNum = 1
        'see if selection moves to any dependents
        If .Address(External:=True) = .NavigateArrow(False, 1, ArrowNum).Address(External:=True) Then GoTo Done
                'otherwise../
                Do
                     'timestamp if on Sheet1
                    If ActiveSheet.Name = "Sheet1" Then Call TimeStampDependents(Selection, Evnt)
                    'try another arrow
                    ArrowNum = ArrowNum + 1
                    .NavigateArrow False, 1, ArrowNum
                ' stop if that arrow doesn't exist
                Loop Until Err.Number <> 0
Done:
    .Parent.ClearArrows
    'return to original selection
    .Parent.Activate
    SelRange.Select
End With


End Sub

Note that it deliberately tries to find more dependents than there are and relies on the failure to find them to cause an error and to exit the sub. The variable Evnt is declared outside the code and is passed to this code (behind Sheet2)  which is almost identical to the Sheet1 macro above but with just three changes in bold below:

Private Sub TimeStampDependents(ByVal Target As Range, Evnt As String)
'Timestamp Data
'   TeachExcel.com

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

With Sheet1
    'Your data table range
    Set myTableRange = .Range("A2:B8")

    'Check if the changed cell is in the data tabe or not.
    If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

    'Stop events from running
    Application.EnableEvents = False

    'Column for the date/time
    Set myDateTimeRange = .Range("C" & Target.Row)
    'Column for last updated date/time
    Set myUpdatedRange = .Range("D" & Target.Row)

    'Determine if the input date/time should change
    If myDateTimeRange.Value = "" Then

        myDateTimeRange.Value = Now

    End If

    'Update the updated date/time value
    myUpdatedRange.Value = Now




    '### State last two causes of timestamps
    With myUpdatedRange.Offset(0, 1)
        .Value = Evnt & " changed; " & .Text
        If InStrRev(.Text, ";") > InStr(.Text, ";") Then
            .Value = Left(.Text, InStr(InStr(.Text, ";") + 2, .Text, ";"))
        End If
    End With

If you change yellow cell D3 on Sheet2, it will timestamp for the two yellow cells on Sheet1 which use it (and add "Labour rate 2 changed" to column E). For example, the formula in B4 is:

=ROUNDUP(1.3*(2*Sheet2!D3+Sheet2!D8),2)

Likewise green cell D7 affects green cells B3, B7 and B8 of Sheet1 (and will add "Material 2 changed" to column E). Other cells in Sheet2 affect one or no cells (but it doesn't matter, the timestamps should be right).

Hope this helps (and you can modify the approach to suit your purposes).. If so, please remember to mark this Answer as Selected.

Discuss

Discussion

Pitty, please respond to the Answers from Willie and me.
John_Ru (rep: 6092) Mar 6, '23 at 5:57 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login