Yes it would, but each sheet would have to have the code placed into its own
code segment. That's a little 'expensive', and there's a less expensive way
to deal with multiple sheets. You could use the workbook's _SheetChange()
event which would 'see' a change taking place in any sheet in the book.
To get into that proper code area, right-click on the Excel icon immediately
to the left of the word File in the menu bar and choose [View Code] from its
list. Copy and paste this code into its code module for starters:
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
'if you use this event handler,
' Sh will represent the worksheet and
' Target represents the cell changed, so
' Sh.Name
' will give you the name of the sheet where
' a change took place, and
' Target.Address
' will give you the address of the cell on the sheet
'
' record the time of changes in
' cell B2 of sheet where it took place:
If Not Application.Intersect(Target, Range("B4:H52")) _
Is Nothing Then
Worksheets(Sh.Name).Range("B2") = Now()
End If
End Sub
The only problem with that code is it assumes that you are only interested
in the same range, B4:H52, on every sheet. That may not be the case. You
can modify the code to add some testing to see which sheet the change took
place in and adjust the range tested for change accordingly:
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim testRange As Range ' area to watch
Dim chgCell As Range ' cell to report change in
'make decision based on sheet name
'the Trim() makes certain that we
'don't get fooled if the actual name has
'extra spaces at start/end of it
Select Case Trim(Sh.Name)
Case Is = "Sheet1"
testRange = "B4:H52"
chgCell = "B2"
Case Is = "Sheet2"
'sheet where B2 is in use
testRange = "A1:C20"
chgCell = "D1"
Case Is = "Sheet3"
testRange = "B6:H60"
chgCell = "B2"
Case Is = "Sheet4", "Sheet5"
'2 sheets laid out the same
testRange = "C20:Z45"
chgCell = "B2"
Case Else
'ignore any other sheets
Exit Sub
End Select
If Not Application.Intersect(Target, Range(testRange)) _
Is Nothing Then
'report change time on sheet it took place in
Worksheets(Sh.Name).Range(chgCell) = Now()
End If
End Sub
"shasta2711" wrote:
> That worked absolutely perfect thank you very much.
>
> Would this be a simular code if I wanted to use this for a large worksheet
> with multiple sheets as well?
>
> "JLatham" wrote:
>
> > Put this code into the worksheet's code area:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Not Application.Intersect(Target, Range("B4:H52")) _
> > Is Nothing Then
> > Range("B2") = Now()
> > End If
> > End Sub
> >
> > To put it where it needs to go, choose the sheet and right-click on the
> > sheet's name tab and choose [View Code] from the popup list, copy the code
> > and paste it into the module presented to you. Any time a changes is made in
> > any cell in the range B4:H52, the time of that change will be put into B2.
> >
> > "shasta2711" wrote:
> >
> > > I need to setup a document to show the date and time of any changes to the
> > > document. Date and time will be in cell 2B the information being tracked are
> > > in cells 4B through 52H.
> > >
> > > I'm kind of new at this time of programing if anyone can help, I'd sure
> > > appreciate it.
> > >
> > >
|
Similar Threads
