To Ed, Neil, Bob and Patrick,
Thanks to all of you for the great tips, I didn't know there was so many
ways to do the same thing ;-0
I'll be able to put to use some of the other tips I also learned from your
coding.
Anyway thanks,
James

"Patrick Molloy" wrote in message
...
You can achieve this easily with the worksheet change event in
ThisWorkbook's code page. Its like a change event in a sheet, but its at
the
workbook level. This means that not only is the changed cell passed to the
event handler, so is the worksheet.

In the VBE, in the Project viewer, double click on ThisWorkbook to open
the
code page and paste this:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim LogTarget As Range

On Error GoTo error_trap

' check its not the log - if it is kick out...
If Sh.Name = "log" Then Exit Sub

' get the next row in the log file
Set LogTarget = Worksheets("log").Range("A65000").End(xlUp).Offset (1,
0)

' enter the log data :
LogTarget.Value = Format(Now, "dd-mm-yy HH:MM")
LogTarget.Offset(0, 1) = Sh.Name
LogTarget.Offset(0, 2) = Target.Address
LogTarget.Offset(0, 3) = Target.Value

Exit Sub
error_trap:
MsgBox Err.Description

End Sub


What we expect is to find a sheet called "log". We test that this isn't
the
sheet name being passed to prevent an endless loop resulting in a stack
overflow (geeky huh <vbg)
Any way, if the changed sheet isn't the log sheet itself, the the next
available row on the log sheet is found and data regarding the change is
placed there.


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"James" wrote in message
ink.net...
Hello NG,
can someone please help me with problem in regards to referencing
another
worksheet?
I have a worksheet called "log" which keeps track of the date the last
time
the workbook was modified.
I also have many worksheets for each month of the years, (i.e. jan2004,
feb2004, mar2004...).
What I would like to do is display a message showing the date the last
time
the workbook is modified.
I can get everything to work but I always end up on the log sheet, how
can
I
sat on the current month's page and reference the Log sheet?
Any suggestions would be greatly appreciated.
Thanks
James

This works============================================= =======

Sub LastModified()

Dim datLastModified As Date
Dim datToday As Date
Dim intLastCell As Integer

datToday = Date
Application.ScreenUpdating = False

Worksheets("Log").Select
Range("=Log!A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
datLastModified = ActiveCell.Value
ActiveCell.Offset(1, 0) = datToday

Application.ScreenUpdating = True

MsgBox "Last modified on: " & datLastModified, vbOKOnly, "Last Modified"

End Sub