Last data entry made time and Date show in each worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rajat - 11 Nov 2006 06:46 GMT

Hi,

i've created a Excell file and there are atleast 55 worksheet and i've named
them as 1, 2, 3 to 55 etc. but the content of the each worksheet is same
(means data entry field are same in each sheet) its basically a templet for
entering specific information of seperate projects Print area of each sheet
is A1:Y74. At one go all the information are not filled up in each sheet, it
will be updated regularly. I've used the Tools>Protection>Project Sheet
option so that the user can access only those fields where they have to enter
DATA.

My requirement is i want to know when last data entry (not just simple
saving of sheet) was made in each sheet and saved that time and date to
appear in a specific cell of each sheet say A75 for Time and A76 for Date.

Because there will be instance when one user will open the file and enter
the updated data related to projects in 2 or 3 sheet out of 55 and closed the
file in that instance i want to see that the sheet where updation was made
those show the time and date in that sheet and other sheets remaim unchaged
untill those are updated.

Thanx in advance,

Reply to this Message

Bob Phillips - 11 Nov 2006 10:09 GMT

You could add this to your workbook which will record changes to a Master
sheet

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const MASTER_SHEET As String = "MAster" '<=== change to suit
Dim wsmaster As Worksheet
Dim iRow As Long

Set wsmaster = Worksheets(MASTER_SHEET)
If Sh.Name <> wsmaster.Name Then
iRow = wsmaster.Cells(wsmaster.Rows.Count, "A").End(xlDown).Row + 1
wsmaster.Cells(iRow, "A").Value = Sh.Name
wsmaster.Cells(iRow, "B").Value = Target.Address(False, False)
wsmaster.Cells(iRow, "C").Value = Environ("UserName")
End If

End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi,
>
[quoted text clipped - 18 lines]
>
> Thanx in advance,

Reply to this Message

Rajat - 11 Nov 2006 13:16 GMT

Dear Bob

thanx for ur effort, but i faced few problems and would try to clarify it,

i've copied the code but it gave me a 'Run Time Error 9'
I am not an expet in VBA, as mentioned by you to modify the "MAster" i've
put the Print area of the worksheet $A$1:$Y$74 the same run time error i got
and the line
"Set wsmaster = Worksheets(MASTER_SHEET)" was yellow marked

then i changed the "MAster" with the tab names (i have 55 sheets those are
named 1, 2, 3 ... to 55) '1'!:'55'! but same error occured.

my requirement is i want to know when last data entry was made in each sheet
i.e. 1, 2, 3, etc that time and Date to be displayed in that perticular
sheet's specific cell i.e. for worksheet named '1' Time shown in Cell
'1'!$A$75 and Date shown in Cell '1'!$A$76.

am i able to explain you the problem Bob, hope taht you may sort it out
quickly.

Reply to this Message

Bob Phillips - 12 Nov 2006 13:27 GMT

Didn't cater for an empty sheet.

Try this better version

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const MASTER_SHEET As String = "Master" '<=== change to suit
Dim wsMaster As Worksheet
Dim cRows As Long
Dim iRow As Long

Set wsMaster = Worksheets(MASTER_SHEET)
If Sh.Name <> wsMaster.Name Then
cRows = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
If cRows >= wsMaster.Rows.Count Then
wsMaster.Cells(1, "A").Value = "Sheet"
wsMaster.Cells(1, "B").Value = "UserName"
wsMaster.Cells(1, "C").Value = "Timestamp"
wsMaster.Cells(1, "D").Value = "Cell"
wsMaster.Cells(1, "E").Value = "Value"
wsMaster.Rows(1).Font.Bold = True
iRow = 2
Else
iRow = cRows + 1
End If
wsMaster.Cells(iRow, "A").Value = Sh.Name
wsMaster.Cells(iRow, "B").Value = Environ("UserName")
wsMaster.Cells(iRow, "C").Value = Format(Now, "dd-mmm-yyyy
hh:mm:ss")
wsMaster.Cells(iRow, "D").Value = Target.Address(False, False)
If Target.HasFormula Then
wsMaster.Cells(iRow, "E").Value = "'" & Target.Formula
Else
wsMaster.Cells(iRow, "E").Value = Target.Text
End If
wsMaster.Columns("A:E").AutoFormat
End If

End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Dear Bob
>
[quoted text clipped - 16 lines]
> am i able to explain you the problem Bob, hope taht you may sort it out
> quickly.

Reply to this Message