When changing document information how do you show the date and t.

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

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.

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.

 

[Guest]

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

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?

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.

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.

 

[Guest]

Thank you very much I have made a copy of one of our larger spreadsheet and
will enter the code in it first I'm sure that between all the wonderful
information in the examples I will be able to do what I need to do to track
date when the document is modified.

Again thank you

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

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?

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.

:

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.

 

[frustrated]

I need to track some changes in a cell as well. I want the date to populate
in a column right next to it. I am not sure how to use the VBA to paste code
etc. Can you walk me through exactly what I should do, what do I click etc
when VBA comes up.

THANK YOU IN ADVANCE!
FRUSTRATED!

 

[Rick Rothstein \(MVP - VB\)]

I am assuming this is a new question as I cannot see any part of the thread
you replied to (hence, I assume it is old).

You can use this event code to do what you asked...

Private Sub Worksheet_Change(ByVal Target As Range)
Const AddressToTrack As String = "C4"
If Not Intersect(Target, Range(AddressToTrack)) Is Nothing Then
Target.Offset(, 1).Value = Now
End If
End Sub

You didn't tell us what cell (or cells assuming you "simplified" your
question for us) that you wanted to track, so I assumed C4 for my example.
Change the "C4" assignment to the AddressToTrack constant to the address for
the cell or cells you want this code to apply to. To install the code, right
click the tab at the bottom of the worksheet containing the cells you want
to track and select View Code from the menu that pops up; then copy/paste
the above code into the code window that appeared. Now, go back to the
worksheet and enter something into (one of) the cell(s) you are tracking...
the date and time will be placed into the cell next to it.

Rick

 

[frustrated]

Thanks rick.. I want it to monitor all cells in column "C" and if a cell in
column C is editied, to populate the date edited in column "G".. when I go
into VBA the larger part of the screen is "gray" and there are 2 boxes on the
left.. where do I paste or what do i do to bring up something to paste into.

 

[frustrated]

Rick...THANK YOU... I FIGURED IT OUT!!! YOU ARE A LIFESAVER!