Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time stamp
How do I create a time stamp in a cell for an adjacent cell event
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time stamp
Right-click on a sheet tab and paste this code into the window that opens:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$b$400")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") .Select .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "mm/dd/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = InputBox("You've made a change to the Rates tab. Please enter your name here for historical purposes.") Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub Here is another option to try: Dim vOldVal 'Must be at top of module Private Sub Worksheet_Change(ByVal Target As Range) Dim bBold As Boolean Dim rArea As Range Dim rCell As Range For Each rArea In Target.Areas For Each rCell In rArea 'your individual cell code here Next rCell Next rArea If Target.Cells.Count 1 Then Exit Sub On Error Resume Next With Application .ScreenUpdating = False .EnableEvents = False End With If IsEmpty(vOldVal) Then vOldVal = "Empty Cell" bBold = Target.HasFormula With Sheet1 .Unprotect Password:="Secret" If .Range("A1") = vbNullString Then .Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _ "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE") End If With .Cells(.Rows.Count, 1).End(xlUp)(2, 1) .Value = Target.Address .Offset(0, 1) = vOldVal With .Offset(0, 2) If bBold = True Then .ClearComments .AddComment.Text Text:= _ "OzGrid.com:" & Chr(10) & "" & Chr(10) & _ "Bold values are the results of formulas" End If .Value = Target .Font.Bold = bBold End With .Offset(0, 3) = Time .Offset(0, 4) = Date End With .Cells.Columns.AutoFit '.Protect Password:="Secret" End With vOldVal = vbNullString With Application .ScreenUpdating = True .EnableEvents = True End With On Error GoTo 0 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) vOldVal = Target End Sub One more version: Function MyUserName() As String MyUserName = Environ("UserName") End Function Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$BB$4000")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") ..Select ..Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "mm/dd/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = MyUserName() Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub Regards, Ryan--- -- RyGuy "Bally" wrote: How do I create a time stamp in a cell for an adjacent cell event |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time stamp
For use in multiple columns.
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: If Intersect(Range(Target(1).Address), _ Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub Application.EnableEvents = False Target.Offset(0, 1).Value = Now() ws_exit: Application.EnableEvents = True End Sub For one column............... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: If Intersect(Range(Target(1).Address), _ Range("B:B")) Is Nothing Then Exit Sub Application.EnableEvents = False Target.Offset(0, 1).Value = Now() ws_exit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste into that sheet module, Make any edits you wish then Alt + q to go back to the Excel window. Gord Dibben MS Excel MVP On Wed, 9 Apr 2008 08:10:01 -0700, Bally wrote: How do I create a time stamp in a cell for an adjacent cell event |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Stamp | Excel Discussion (Misc queries) | |||
Time Stamp | Excel Discussion (Misc queries) | |||
Time Stamp | Excel Worksheet Functions | |||
Time Stamp | Excel Discussion (Misc queries) | |||
Time Stamp | Excel Discussion (Misc queries) |