sheet update date
 

I have a excel workbook with many sheets and in my master sheet i hv all the
sheet names in one column, i would like to have macro code ,so that if
anybody type any comments on any sheets the date need to be appeared on my
master sheet next to sheet name column.

Example
Master Sheet:
sheetname
sheet1
sheet2
if somebody open the excel file and update the comments on sheet2 and the
file is saved and closed.

when i look into my master file, the comments updated date should appear
next to column of sheet names
Master Sheet:
sheetname comment Date
sheet1
sheet2 May 27th 2009 11:15pm

sheet update date
 

use the code page behind ThisWorkbook, and add this code:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rw As Long
Dim indexlist As Range
If Sh.Name = "Master Sheet" Then Exit Sub
Set indexlist = Worksheets("Master Sheet").Range("A1:A100")
On Error Resume Next
rw = Application.WorksheetFunction.Match(Sh.Name, indexlist, False)
If Err.Number < 0 Then
Err.Clear
Else
Worksheets("Master Sheet").Range("A1").Offset(rw - 1, 1) = Now
End If
End Sub



"Ranjith Kurian" wrote in message
...
I have a excel workbook with many sheets and in my master sheet i hv all
the
sheet names in one column, i would like to have macro code ,so that if
anybody type any comments on any sheets the date need to be appeared on my
master sheet next to sheet name column.

Example
Master Sheet:
sheetname
sheet1
sheet2
if somebody open the excel file and update the comments on sheet2 and the
file is saved and closed.

when i look into my master file, the comments updated date should appear
next to column of sheet names
Master Sheet:
sheetname comment Date
sheet1
sheet2 May 27th 2009 11:15pm

sheet update date
 

Give this workbook event code a try...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim C As Range
Set C = Worksheets("Master Sheet").Columns("A").Find(Sh.Name)
If Not C Is Nothing Then
C.Offset(, 1).Value = Now
End If
End Sub

To install this event code, right click the XL icon immediately to the left
of the File item on Excel's menu bar, select View Code from the popup menu
that appeared, and then copy/paste the above code into the code window that
opened up.

Note that the above code assumes your sheet names are located in Column A
and that the Master Sheet's name does *not* appear anywhere in Column A...
it was unclear from your post whether the "Master Sheet:" designation were
just identifying the sheet or whether it was actually part of your header.
IF the Master Sheet's name *does* appear in the header (assumed to be
located in Row 1), then you can use this variation of the code instead...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim C As Range
Set C = Worksheets("Master Sheet").Columns("A").Find(Sh.Name)
If Not C Is Nothing Then
If C.Row 1 Then C.Offset(, 1).Value = Date
End If
End Sub

--
Rick (MVP - Excel)


"Ranjith Kurian" wrote in message
...
I have a excel workbook with many sheets and in my master sheet i hv all
the
sheet names in one column, i would like to have macro code ,so that if
anybody type any comments on any sheets the date need to be appeared on my
master sheet next to sheet name column.

Example
Master Sheet:
sheetname
sheet1
sheet2
if somebody open the excel file and update the comments on sheet2 and the
file is saved and closed.

when i look into my master file, the comments updated date should appear
next to column of sheet names
Master Sheet:
sheetname comment Date
sheet1
sheet2 May 27th 2009 11:15pm

sheet update date
 

I guess to protect against the possibility of one of your sheet names being
part of another sheet's name, we should restrict the Find operation to the
cell's entire content. Change the Set statement in whichever event code
procedure you used to this...

Set C = Worksheets("Master Sheet").Columns("A"). _
Find(Sh.Name, LookAt:=xlWhole)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this workbook event code a try...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim C As Range
Set C = Worksheets("Master Sheet").Columns("A").Find(Sh.Name)
If Not C Is Nothing Then
C.Offset(, 1).Value = Now
End If
End Sub

To install this event code, right click the XL icon immediately to the
left of the File item on Excel's menu bar, select View Code from the popup
menu that appeared, and then copy/paste the above code into the code
window that opened up.

Note that the above code assumes your sheet names are located in Column A
and that the Master Sheet's name does *not* appear anywhere in Column A...
it was unclear from your post whether the "Master Sheet:" designation were
just identifying the sheet or whether it was actually part of your header.
IF the Master Sheet's name *does* appear in the header (assumed to be
located in Row 1), then you can use this variation of the code instead...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim C As Range
Set C = Worksheets("Master Sheet").Columns("A").Find(Sh.Name)
If Not C Is Nothing Then
If C.Row 1 Then C.Offset(, 1).Value = Date
End If
End Sub

--
Rick (MVP - Excel)


"Ranjith Kurian" wrote in
message ...
I have a excel workbook with many sheets and in my master sheet i hv all
the
sheet names in one column, i would like to have macro code ,so that if
anybody type any comments on any sheets the date need to be appeared on
my
master sheet next to sheet name column.

Example
Master Sheet:
sheetname
sheet1
sheet2
if somebody open the excel file and update the comments on sheet2 and the
file is saved and closed.

when i look into my master file, the comments updated date should appear
next to column of sheet names
Master Sheet:
sheetname comment Date
sheet1
sheet2 May 27th 2009 11:15pm