Date Stamping a Cell Modification

[Poundland]

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]

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]

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]

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]

I always have error handling Simon. Better safe than sorry, and getting stranded with events turned off.

[Poundland]

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]

An alternative approach
http://vbaexpress.com/kb/getarticle.php?kb_id=909


lenze