software

I want to track the date last changed of several cells, so anyone looking at
the information can easily see the last updated date. I want the date last
changed to show in an adjucant cell. I don't just want to use track changes.

--
TJ

Take a look he

www.mcgimpsey.com/excel/timestamp.htmlIn article gt;,
TJ gt; wrote:

gt; I want to track the date last changed of several cells, so anyone looking at
gt; the information can easily see the last updated date. I want the date last
gt; changed to show in an adjucant cell. I don't just want to use track changes.

Assuming the change is initiated manually, you can use a macro to do this.

Right click on the worksheet tab and select view code. in the resulting
module, in the dropdowns at the top of the module, in the left dropdown
select worksheet and in the right dropdown select Change (not Selection
Change). You will get an event declaration like:Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

this macro will fire when a cell is edited (whether the value is actually
changed or not). You can have it update an adjacent cell with the date and
time

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error goto ErrHandler
set rng = Intersect(Target,columns(3))
if not rng is nothing then
Application.EnableEvents = False
for each cell in rng
' if not isempty(cell) then
cell.offset(0,1).Value = Now
cell.offset(0,1).NumberFormat:=quot;mm/dd/yyyy hh:mmquot;
cell.EntireColumn.Autofit
' end if
Next
ErrHandler:
Application.EnableEvents = True
End Sub

As written it reacts to changes in column C. Adjust to suit your needs.

If you want to don't want to record a date if the cell is cleared, then
remove the single quotes in two places.

--
Regards,
Tom Ogilvyquot;TJquot; wrote:

gt; I want to track the date last changed of several cells, so anyone looking at
gt; the information can easily see the last updated date. I want the date last
gt; changed to show in an adjucant cell. I don't just want to use track changes.
gt;
gt; --
gt; TJ

Paste the following code into your worksheet's code page
(Right click on worksheet tab and select Code)

HTH
--
AP

'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Puts date stamp in next column
' if changed cell belongs to range specified py strMyCells

' Change string value according to your needs
Const strMyCells = quot;A1,B2:B10,C:Cquot;
Dim ocell As Range
Application.EnableEvents = False
For Each ocell In Intersect(Target, Range(strMyCells))
ocell.Offset(0, 1).Value = Date
Next ocell
Application.EnableEvents = True
End Sub
'----------------------------------

quot;TJquot; gt; a écrit dans le message de
...
gt; I want to track the date last changed of several cells, so anyone looking
at
gt; the information can easily see the last updated date. I want the date
last
gt; changed to show in an adjucant cell. I don't just want to use track
changes.
gt;
gt; --
gt; TJ
Tom,

Thanks for the information. I used your macro but I receive the below error
when I click in a cell in column quot;Cquot; to make a change:

Compile error:
Block If without End If

Also, I think your code below had one type-o:
cell.offset(0,1).NumberFormat:=quot;mm/dd/yyyy hh:mmquot;
I dont beleive there should be a collon before the equal sign. Maybe this is
my problem but I copied and pasted the macro I used for your reference. It is
exactly the same as yours with the one change I mentioned above.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandler
Set rng = Intersect(Target, Columns(3))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
'if not isempty (cell) then
cell.Offset(0, 1).Value = Now
cell.Offset(0, 1).NumberFormat = quot;mm/dd/yyy hh:mmquot;
cell.EntireColumn.AutoFit
'end if
Next
ErrHandler:
Application.EnableEvents = True
End Sub

Please let me know if you can see any reason why I would be receiving the
error.

Thanks for your help!

Nicole

quot;Tom Ogilvyquot; wrote:

gt; Assuming the change is initiated manually, you can use a macro to do this.
gt;
gt; Right click on the worksheet tab and select view code. in the resulting
gt; module, in the dropdowns at the top of the module, in the left dropdown
gt; select worksheet and in the right dropdown select Change (not Selection
gt; Change). You will get an event declaration like:
gt;
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt;
gt; End Sub
gt;
gt; this macro will fire when a cell is edited (whether the value is actually
gt; changed or not). You can have it update an adjacent cell with the date and
gt; time
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; On Error goto ErrHandler
gt; set rng = Intersect(Target,columns(3))
gt; if not rng is nothing then
gt; Application.EnableEvents = False
gt; for each cell in rng
gt; ' if not isempty(cell) then
gt; cell.offset(0,1).Value = Now
gt; cell.offset(0,1).NumberFormat:=quot;mm/dd/yyyy hh:mmquot;
gt; cell.EntireColumn.Autofit
gt; ' end if
gt; Next
gt; ErrHandler:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; As written it reacts to changes in column C. Adjust to suit your needs.
gt;
gt; If you want to don't want to record a date if the cell is cleared, then
gt; remove the single quotes in two places.
gt;
gt; --
gt; Regards,
gt; Tom Ogilvy
gt;
gt;
gt; quot;TJquot; wrote:
gt;
gt; gt; I want to track the date last changed of several cells, so anyone looking at
gt; gt; the information can easily see the last updated date. I want the date last
gt; gt; changed to show in an adjucant cell. I don't just want to use track changes.
gt; gt;
gt; gt; --
gt; gt; TJ

1. I said don't use selectionchange and then copied it in without noticing.
Anyway, this is tested and works:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Set rng = Intersect(Target, Columns(3))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
' if not isempty(cell) then
cell.Offset(0, 1).Value = Now
cell.Offset(0, 1).NumberFormat = quot;mm/dd/yyyy hh:mmquot;
cell.Offset(0, 1).EntireColumn.AutoFit
' end if
Next
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvyquot;NikkiBquot; wrote:

gt; Tom,
gt;
gt; Thanks for the information. I used your macro but I receive the below error
gt; when I click in a cell in column quot;Cquot; to make a change:
gt;
gt; Compile error:
gt; Block If without End If
gt;
gt; Also, I think your code below had one type-o:
gt; cell.offset(0,1).NumberFormat:=quot;mm/dd/yyyy hh:mmquot;
gt; I dont beleive there should be a collon before the equal sign. Maybe this is
gt; my problem but I copied and pasted the macro I used for your reference. It is
gt; exactly the same as yours with the one change I mentioned above.
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; On Error GoTo ErrHandler
gt; Set rng = Intersect(Target, Columns(3))
gt; If Not rng Is Nothing Then
gt; Application.EnableEvents = False
gt; For Each cell In rng
gt; 'if not isempty (cell) then
gt; cell.Offset(0, 1).Value = Now
gt; cell.Offset(0, 1).NumberFormat = quot;mm/dd/yyy hh:mmquot;
gt; cell.EntireColumn.AutoFit
gt; 'end if
gt; Next
gt; ErrHandler:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; Please let me know if you can see any reason why I would be receiving the
gt; error.
gt;
gt; Thanks for your help!
gt;
gt; Nicole
gt;
gt; quot;Tom Ogilvyquot; wrote:
gt;
gt; gt; Assuming the change is initiated manually, you can use a macro to do this.
gt; gt;
gt; gt; Right click on the worksheet tab and select view code. in the resulting
gt; gt; module, in the dropdowns at the top of the module, in the left dropdown
gt; gt; select worksheet and in the right dropdown select Change (not Selection
gt; gt; Change). You will get an event declaration like:
gt; gt;
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; this macro will fire when a cell is edited (whether the value is actually
gt; gt; changed or not). You can have it update an adjacent cell with the date and
gt; gt; time
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; On Error goto ErrHandler
gt; gt; set rng = Intersect(Target,columns(3))
gt; gt; if not rng is nothing then
gt; gt; Application.EnableEvents = False
gt; gt; for each cell in rng
gt; gt; ' if not isempty(cell) then
gt; gt; cell.offset(0,1).Value = Now
gt; gt; cell.offset(0,1).NumberFormat:=quot;mm/dd/yyyy hh:mmquot;
gt; gt; cell.EntireColumn.Autofit
gt; gt; ' end if
gt; gt; Next
gt; gt; ErrHandler:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; As written it reacts to changes in column C. Adjust to suit your needs.
gt; gt;
gt; gt; If you want to don't want to record a date if the cell is cleared, then
gt; gt; remove the single quotes in two places.
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt; Tom Ogilvy
gt; gt;
gt; gt;
gt; gt; quot;TJquot; wrote:
gt; gt;
gt; gt; gt; I want to track the date last changed of several cells, so anyone looking at
gt; gt; gt; the information can easily see the last updated date. I want the date last
gt; gt; gt; changed to show in an adjucant cell. I don't just want to use track changes.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; TJ

Thank you very much Tom. This works beautifully! Hope you have a great day!

quot;Tom Ogilvyquot; wrote:

gt; 1. I said don't use selectionchange and then copied it in without noticing.
gt; Anyway, this is tested and works:
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; On Error GoTo ErrHandler
gt; Set rng = Intersect(Target, Columns(3))
gt; If Not rng Is Nothing Then
gt; Application.EnableEvents = False
gt; For Each cell In rng
gt; ' if not isempty(cell) then
gt; cell.Offset(0, 1).Value = Now
gt; cell.Offset(0, 1).NumberFormat = quot;mm/dd/yyyy hh:mmquot;
gt; cell.Offset(0, 1).EntireColumn.AutoFit
gt; ' end if
gt; Next
gt; End If
gt; ErrHandler:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; --
gt; Regards,
gt; Tom Ogilvy
gt;
gt;
gt; quot;NikkiBquot; wrote:
gt;
gt; gt; Tom,
gt; gt;
gt; gt; Thanks for the information. I used your macro but I receive the below error
gt; gt; when I click in a cell in column quot;Cquot; to make a change:
gt; gt;
gt; gt; Compile error:
gt; gt; Block If without End If
gt; gt;
gt; gt; Also, I think your code below had one type-o:
gt; gt; cell.offset(0,1).NumberFormat:=quot;mm/dd/yyyy hh:mmquot;
gt; gt; I dont beleive there should be a collon before the equal sign. Maybe this is
gt; gt; my problem but I copied and pasted the macro I used for your reference. It is
gt; gt; exactly the same as yours with the one change I mentioned above.
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; On Error GoTo ErrHandler
gt; gt; Set rng = Intersect(Target, Columns(3))
gt; gt; If Not rng Is Nothing Then
gt; gt; Application.EnableEvents = False
gt; gt; For Each cell In rng
gt; gt; 'if not isempty (cell) then
gt; gt; cell.Offset(0, 1).Value = Now
gt; gt; cell.Offset(0, 1).NumberFormat = quot;mm/dd/yyy hh:mmquot;
gt; gt; cell.EntireColumn.AutoFit
gt; gt; 'end if
gt; gt; Next
gt; gt; ErrHandler:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; Please let me know if you can see any reason why I would be receiving the
gt; gt; error.
gt; gt;
gt; gt; Thanks for your help!
gt; gt;
gt; gt; Nicole
gt; gt;
gt; gt; quot;Tom Ogilvyquot; wrote:
gt; gt;
gt; gt; gt; Assuming the change is initiated manually, you can use a macro to do this.
gt; gt; gt;
gt; gt; gt; Right click on the worksheet tab and select view code. in the resulting
gt; gt; gt; module, in the dropdowns at the top of the module, in the left dropdown
gt; gt; gt; select worksheet and in the right dropdown select Change (not Selection
gt; gt; gt; Change). You will get an event declaration like:
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; this macro will fire when a cell is edited (whether the value is actually
gt; gt; gt; changed or not). You can have it update an adjacent cell with the date and
gt; gt; gt; time
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; gt; On Error goto ErrHandler
gt; gt; gt; set rng = Intersect(Target,columns(3))
gt; gt; gt; if not rng is nothing then
gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; for each cell in rng
gt; gt; gt; ' if not isempty(cell) then
gt; gt; gt; cell.offset(0,1).Value = Now
gt; gt; gt; cell.offset(0,1).NumberFormat:=quot;mm/dd/yyyy hh:mmquot;
gt; gt; gt; cell.EntireColumn.Autofit
gt; gt; gt; ' end if
gt; gt; gt; Next
gt; gt; gt; ErrHandler:
gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; As written it reacts to changes in column C. Adjust to suit your needs.
gt; gt; gt;
gt; gt; gt; If you want to don't want to record a date if the cell is cleared, then
gt; gt; gt; remove the single quotes in two places.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Regards,
gt; gt; gt; Tom Ogilvy
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;TJquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I want to track the date last changed of several cells, so anyone looking at
gt; gt; gt; gt; the information can easily see the last updated date. I want the date last
gt; gt; gt; gt; changed to show in an adjucant cell. I don't just want to use track changes.
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; TJ