View Full Version : Date Stamping a Cell Modification
Poundland
06-23-2008, 09:28 AM
Guys,
I wonder if again you offer me assistance, I am creating a spreadsheet powered by VBA and want to capture a date against a cell that has been modified to show the date the modification took place.
Cell range a1 to a500
Date stamp b1 to b500
Each time a cell in the 'a' range is modified, i would like the corresponding cell in 'b' to capture the date that the modification was done.
Is there some code that can achieve this?
Simon Lloyd
06-23-2008, 09:33 AM
In the worksheet code module you are working with
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1).Value = Date
End Sub
not tested!
Bob Phillips
06-23-2008, 09:34 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A500" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm yyyy"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
Simon Lloyd
06-23-2008, 11:10 AM
Bob, I understand turning events on and off (perhaps i should have thought of that) but why the error handling?, would Excel not just format the date legibly into xx/xx/xxxx or would that cause problems later?
Bob Phillips
06-23-2008, 11:54 AM
I always have error handling Simon. Better safe than sorry, and getting stranded with events turned off.
Poundland
06-26-2008, 04:04 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A500" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm yyyy"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
Thank you for this code.
I want to be able to share this file for multi user update, and protect the date stamp column so they cannot be deleted, how can I amend the code given to accomodate my needs?
lenze
06-26-2008, 08:17 AM
An alternative approach
http://vbaexpress.com/kb/getarticle.php?kb_id=909
lenze
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.