Date and User stamp to show changes in a column that has linked Data from another Worksheet

[Yeft]

Could some one help me with a VBA code for showing Date and User name stamp when any change has been made to (Column A) of my table?
The VBA code I have works when typing in directly the new status in my table, but when Refreshing Data from a Query (Different Table), then new Columns get added
to the table and mess up with headers as well. I need to see all the stamps by just refreshing the Data:

Private Sub Worksheet_Change(ByVal Tgt As Range)

Dim MyData As Range
Dim MyDataRng As Range
Set MyDataRng = ActiveSheet.ListObjects("My Table").ListColumns(3).DataBodyRange

If Intersect(Tgt, MyDataRng) Is Nothing Then Exit Sub

On Error Resume Next
If Tgt.Offset(0, 1) = "" Then
Tgt.Offset(0, 1) = Now

Else
Tgt.Offset(0, 2) = Now
Tgt.Offset(0, 3) = Aplication.UserName


End If

For Each MyData In MyDataRng
If MyData = "" Then
MyData.Offset(0, 1).ClearContents
End If

Next MyData

End Sub

 

[Joe4]

"Worksheet_Change" procedures are only fired when data is manually updated.
They are not fired when calculations run or values return by links or formulas change.

There is a "Worksheet_Calculate" procedure that fires whenever any calculation is run on a sheet, but the big caveat is that it cannot tell what value was updated or where.
So, you couldn't really use this to create a row-level date/time stamp.

So I don't think you are going to be able to simply "refresh" the data and get time stamps from the linked values that change.
The place to add the date stamps would be the source of the data, assuming those values are manually updated.

 

[Yeft]

"Worksheet_Change" procedures are only fired when data is manually updated.
They are not fired when calculations run or values return by links or formulas change.

There is a "Worksheet_Calculate" procedure that fires whenever any calculation is run on a sheet, but the big caveat is that it cannot tell what value was updated or where.
So, you couldn't really use this to create a row-level date/time stamp.

So I don't think you are going to be able to simply "refresh" the data and get time stamps from the linked values that change.
The place to add the date stamps would be the source of the data, assuming those values are manually updated.

Hello Joe4,
The source Data is a shared file via Teams, and Users open it and type changes that way. I tried to write the code, but unfortunately these shared files don't support macros.
I noticed that the code would work out only if people open the file as an App, but unfortunately there is no guarantee that people would do it, so changes done
directly from file via Teams won't update, so regardless the way they open the file I need to capture those stamps.
Please help me to figure this out regardless the way they open that file and enter that Data.
Thanks

 

[Joe4]

Take a look at method 3 here, which shows you how you can create Date stamp without VBA.

How to Insert Excel Timestamp When Cell Changes Without VBA (3 Ways) - ExcelDemy

This article describes 3 easy ways to insert excel timestamp when cell changes without vba. Use these methods to solve your problems easily.

www.exceldemy.com

That is the only way I have seen to do it without VBA.

 

[Yeft]

Take a look at method 3 here, which shows you how you can create Date stamp without VBA.

How to Insert Excel Timestamp When Cell Changes Without VBA (3 Ways) - ExcelDemy

This article describes 3 easy ways to insert excel timestamp when cell changes without vba. Use these methods to solve your problems easily.

www.exceldemy.com

That is the only way I have seen to do it without VBA.

Hey Joe4,

I've tried and unfortunately none of those methods worked out . I think there must be still a way to solve it.
Please let me know if there could be something else I should try.

Thank you,

 

[Joe4]

The only other possible thing I can think of is to try to use Office Scripts instead.
From my search on-line, I found the following:
"Office Scripts can work on Excel files within Microsoft Teams, but only when accessing Teams through the web browser; you cannot run Office Scripts directly within the Teams desktop app for Windows, Mac, iOS, or Android."

Here is a link I found on creating Date Stamps with Office Scripts:

ExcelScript.DatetimeFormatInfo interface - Office Scripts

Defines the culturally appropriate format of displaying numbers. This is based on current system culture settings.

learn.microsoft.com

I have never used Office Scripts myself, so unfortunately cannot provide any support on using them.

 

[Yeft]

Hi Joe4,

Ok, I'll try to figure it out. However thanks for taking the time in researching and helping.

Regards,