Recording the time changes are made in multiple cells

[BobTheSpreadsheetBuilder]

Hi guys, I am experienced with vba, not so much with sheet events.
In a grid of cells, say A1:J10 a number of users might update single cells at a time. I plan to track the changes by using a procedure to update a sheet2 with Date+Time in the corresponding cell in the A1:J10 grid in sheet2. Do i need to run a separate event procedure for each cell, or can i use a single procedure ? Thanks

 

[Micron]

Open the vb editor. Double click on a sheet from the project objects pane on the left. If you pick one that already has code in it, you'll see that code. Regardless, in the top left drop down over the code page you'll probably see "General" - choose Worksheet instead. You'll probably see that it chooses an event and writes the opening and closing procedure lines. To me, that's a minor annoyance - let me pick fer cryin' out loud.

The right side drop down will list the worksheet events you can use. You'll want to use a sheet event for this - Change. If the changed cell is outside of the desired range, just exit the sub. I would just use Now() , not Date() and Time() as you seem to be suggesting, unless for some reason you need to split those portions.

EDIT - that does not apply if the changes are made by recalculating the sheet. For that you'd need the Calculate event - or both if changes can be made either way.

Worksheet.Change event (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Artik]

For the good start, the procedure in the Sheet1 module. It is simple, although it is not without flaws.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Me.Range("A1:J10")) Is Nothing Then
        ThisWorkbook.Worksheets("Sheet2").Range(Target.Address).Value = Now
    End If
    
End Sub

Artik

 

[bebo021999]

or, in case copy range (more than 1 cell) as one;

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Intersect(Target, Range("A1:J10")) Is Nothing Then Exit Sub
For Each cell In Target
    With Sheets("Sheet2").Range(cell.Address)
        .Value = Now
        .EntireColumn.AutoFit
    End With
Next
End Sub

 

[BobTheSpreadsheetBuilder]

Thank you guys for this. This is my first post here. I tried Artik's solution and its working perfectly. Didn't need to try bebo's solution. You guys are fantastic

 

[Micron]

Hi guys, I am experienced with vba

When someone says that, I approach it as if they would best benefit from a fishing pole and not just the fish. Hope you learned something anyway.

 

[BobTheSpreadsheetBuilder]

Thank you Micron . I followed your instructions and used your suggested option of Now(). I may experiment with the Calculate event at some stage.

This advice has allowed me to run a standard vb script to send confirmation emails to their supervisors whenever up to 100 students complete up to 15 assignments.

I will happily accept a fish or a fishing pole...i had bought the Excel Professional Development book but my eyesight deteriorated so i can't read it clearly - do all my learning online now. Thanks again

 

[BobTheSpreadsheetBuilder]

Thank you Micron... i just posted a long response to you but it's disappeared

 

[Micron]

When my posts are long, I often ctrl+a to select all of it (unless that copies the entire page) and ctrl+c to copy just in case. Sometimes software does bad things to your posts. Too bad that happened because I'm the curious sort.