Worksheet changes (XL 2000 SP3)

Topic

New Reply

I have my UsageLog This Thread working for file opened, file saved, and username, but I cannot get it to record when changes are made anywhere in the workbook.

I tried:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

ActiveWorkbook.Worksheets(“UsageLog”).Range(“A65536”).End(xlUp).Offset(0, 1).Value = Time$ & Space(5) & Date$

End Sub

saved in ThisWorkbook, without success, so I tried the simpler:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

MsgBox “Range ” & Target.Address & “was changed”

End Sub

Taken from John Walkenbach, but again no success. Can anyone help please?

Reply | Quote

Replies

The Worksheet_Change event procedure should *not* go into ThisWorkbook, but – as the name indicates – into the worksheet module for each worksheet.

Reply | Quote

Is there anything wrong in altering it to look like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ActiveWorkbook.Worksheets(“UsageLog”).Range(“A65536”).End(xlUp).Offset(0, 1).Value = Time$ & Space(5) & Date$
End Sub

This will work across all sheets now.

Reply | Quote

Yep, that’s easier.

Reply | Quote

Thanks Hans and Rudi

Having to put the code in each sheet would be a bit of a drag, but Rudi’s code works beautifully.

Will someone explain to me what the (ByVal Sh As Object, ByVal Target As Range) does?

Reply | Quote

When the worksheet change event fires, Excel passes the sheet that was changed and the cell or cells that were changed in those two parameters. Otherwise, the code would only know that one or more cells somewhere in the workbook had changed.

Reply | Quote