Vai al contenuto principale

Come inserire il timestamp corrente quando i dati in un'altra cella cambiano in Excel?

Autore: Silvia Ultima modifica: 2020-06-16

Supponendo di ricevere un foglio di lavoro che richiede di modificare i dati in una determinata colonna. E dopo aver modificato il foglio di lavoro, 猫 necessario inserire il timestamp modificato per far sapere agli altri quali celle sono state modificate. Come affrontarlo? Questo articolo ti mostrer脿 il metodo per inserire il timestamp corrente quando i dati in un'altra cella cambiano in Excel come mostrato nell'immagine sottostante.

Inserisci il timestamp corrente quando i dati in un'altra cella cambiano con il codice VBA


Inserisci il timestamp corrente quando i dati in un'altra cella cambiano con il codice VBA

Come mostrato nell'immagine sottostante, 猫 necessario popolare il timestamp nella colonna E se le celle corrispondenti nella colonna C vengono modificate. Si prega di fare quanto segue.

1. Nel foglio di lavoro 猫 necessario modificare e contrassegnare con timestamp, fare clic con il pulsante destro del mouse sulla scheda del foglio e quindi fare clic su Visualizza codice dal menu di scelta rapida.

2. Quindi copia e incolla il codice VBA sottostante nel file Code finestra del Microsoft Visual Basic, Applications Edition finestra. Vedi screenshot:

Codice VBA: inserisci il timestamp corrente quando i dati in un'altra cella cambiano

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If
End Sub

Note:: Nel codice, il numero 3 significa che modificherai i dati nella colonna C e 5 indica che il timestamp verr脿 inserito nella colonna E. Modificali in base alle tue esigenze.

3. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

D'ora in poi, quando si modificano i dati nella colonna C, il timestamp verr脿 popolato nelle celle corrispondenti nella colonna E come mostrato nell'immagine sottostante.

Tipo. Date picker. Inserisci data utilit脿 di Kutools for Excel ti aiuta a inserire rapidamente la data con il formato della data specificato in una cella selezionata.
Scaricalo e provalo ora! (Percorso gratuito di 30 giorni)


Articoli correlati:

I migliori strumenti per la produttivit脿 in ufficio

馃 Assistente AI di Kutools: Rivoluziona l'analisi dei dati basandosi su: Esecuzione intelligente   |  Genera codice  |  Crea formule personalizzate  |  Analizzare i dati e generare grafici  |  Richiama le funzioni di Kutools...
Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli    VLookup a valori multipli  |   VLookup su pi霉 fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilit脿 delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalit脿 in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule    Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle    Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   50+ Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'et脿 in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalit脿 avanzate per aumentare la produttivit脿 e risparmiare tempo.  Fai clic qui per ottenere la funzionalit脿 di cui hai pi霉 bisogno...

Descrizione


Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea pi霉 documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttivit脿 del 50% e riduce ogni giorno centinaia di clic del mouse!
Comments (60)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hallo!

Der VBA-Code funktioniert wunderbar. Gibt es eine M枚glichkeit den Code zu erweitern?

Jetzt gerade sie der Coade wie folgt aus:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 10
xTimeColumn = 16
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub

Allerdings m枚chte ich gerne, dass ein Zeitstempel auch dann ausgegeben wird, wenn in Spalte 3, 4, 6, 8, 10 oder 13 eine 脛nderung erfolgt. WElche 脛nderung spielt keine Rolle, nur das es eine gab. Geht das?

Liebe Gr眉脽e
Paolo :-)
This comment was minimized by the moderator on the site
Hi OP,

Not sure if you are still seeing this. I am trying to update this so that the formula picks up a range. I want Col E:I to be updated with a timestamp if Col J:N update to "Completed". Do you have the code for that?

Thanks,
Huzefa A.
This comment was minimized by the moderator on the site
Hi Huzefa,

I don't know if I understand your question clearly. The following VBA code may help. Please give it a try.
The relationship is a 1:1 mapping between the columns. With this script, when a cell in column J is updated to "Completed", the timestamp will be inserted into the corresponding cell in column E. Similarly, updates to column K will affect column F, and so on.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230816
    Dim IntersectRng As Range
    Dim TargetColumn As Integer

    ' Check if the changed cell is in columns J to N
    Set IntersectRng = Application.Intersect(Target, Me.Range("J:N"))

    If Not IntersectRng Is Nothing Then
        For Each cell In IntersectRng
            ' If the changed cell's value is "Completed"
            If cell.Value = "Completed" Then
                ' Calculate the target column in E:I
                TargetColumn = cell.Column - 5
                Me.Cells(cell.Row, TargetColumn).Value = Now()
            End If
        Next cell
    End If
End Sub
This comment was minimized by the moderator on the site
How would I go about running this Macro in all of the sheets in my workbook? It is only working in the first sheet.
This comment was minimized by the moderator on the site
Hi Jon,
The following VBA code can help. Please give it a try.
Note: This VBA code needs to be added to the ThisWorkbook (Code) window.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/Thisworkbook.png
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Updated by Extendoffice 20221208
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If
End Sub
This comment was minimized by the moderator on the site
Hallo !
Ich habe noch eine Frage zu dem oben angegebenen VBA-Code.
Bei meiner Tabelle befindet sich der Wert, f眉r den der Zeitstempel erstellt werden soll in Spalte G.
Der Wert in G errechnet sich aus der Summe der Werte in den Spalten K:AA.
Die Werte in K:AA ergeben sich aus einem anderen Tabellenblatt, unter Benutzung der Z盲hlenwenn-Funktion.
Wenn die Werte direkt in K:AA eingegeben werden aktualisiert sich der Zeitstempel wie gew眉nscht.
Wenn die Werte in K:AA durch "Z盲hlenwenn" aus einem anderen Tabellenblatt aktualisiert werden, 盲ndert sich der Zeitstempel nicht.

Hat jemand eine L枚sung f眉r mein Problem ?
This comment was minimized by the moderator on the site
Hello Michael Rasche,
The formula issue cannot be handled yet. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Hallo,
ich m枚chte auch gerne in einer Tabelle einen Zeitstempel setzen, und bin bei der Suche auf diese Seite gestossen.
Ich habe alles wie oben beschrieben eingegeben, jetzt ergibt sich noch folgendes Problem:
Der Zeitstempel soll sich 盲ndern, wenn sich der Wert in Spalte G 盲ndert, der Wert in Spalte bildet sich aus der Summe mehrerer Zellen (z.B. K 150 - AA 150) in der Reihe.
Wenn ich einen neuen Wert direkt in die Zellen eingebe funktioniert das VBA.
Bei meiner Tabelle 盲ndern sich die Werte in z.B. K 150 - AA 150 aber durch die "Z盲hlenwenn" Funktion aus einem anderen Tabellenblatt, wenn sich dann die Summe erh枚ht, 盲ndert sich der Zeitstempel nicht.
Hat jemand eine L枚sung f眉r mich ?
This comment was minimized by the moderator on the site
how do I add this on two columns? to have two timestamps for two column on input???
This comment was minimized by the moderator on the site
Hi, finally I found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
This comment was minimized by the moderator on the site
Hi, finally i found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
This comment was minimized by the moderator on the site
Hi, This is great info, but I'm having problem. I would like to use for change in column A time stamp column B and change in column D with time stamp column E and change in column G with time stamp column H. I can not figure out. Help please.
This comment was minimized by the moderator on the site
Hi, Is there a chance you can help me? I have tried the VBA code for the timestamp:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub

My problem is that I have to add dates in column 22 and I need the timestamp in column 21. The above code does not work in my sheet. I have tried it with timestamp functions and formulas, but it always changes it to the date when I opened the workbook.
I also tried to work with conditional formatting that the another cell changes the colour when the cell with the timestamp is not todays date, but this does not work either. Can you let me know what I do wrong?
This comment was minimized by the moderator on the site
Hi Lina,
Do you mean manually enter dates in column 22 and then populate timestamp automatically in adjacent cells in column 21?
You just need to change the column numbers in the code to get it down. If you only want to display time, replace the NOW() with Time() in the code. See the below code.

<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20201106
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 22
xTimeColumn = 21
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Time()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Time()
End If
Next
End If
End If
End Sub
This comment was minimized by the moderator on the site
Thank you for your response. Yes, that's what I wanted to do. I tried the above VBA code before but for some reason it did not work, but it works fine now. Thank you for your help
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations