Time Stamp

[Guest]

I am using the following code to create a time stamp in a cell. I am using
the code so that the date will remain static. Problem is even when you clear
an entry or apply formatting in a cell in column a; it applys a new time
stamp to column 5. How can I change this code to only update when the cell is
entered with data?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
Target.Offset(0, 5).Value = Date + 10
End If

 

[JE McGimpsey]

see

http://www.mcgimpsey.com/excel/timestamp.html

 

[Guest]

thanks!

see

http://www.mcgimpsey.com/excel/timestamp.html

 

[jrm]

Hi! how do i "stamp" the macro to an active cell? I used the code you
suggested:

Public Sub DateTimeStamp(ByVal ChangedCells As Range, _
Optional ByVal IncludeDate As Boolean = True, _
Optional ByVal IncludeTime As Boolean = True, _
Optional ByVal DTFormat As String = "dd mmm yyyy hh:mm", _
Optional ByVal RowOffset As Long = 0&, _
Optional ByVal ColOffset As Long = 1&, _
Optional ByVal ClearWhenEmpty As Boolean = True)
Const n1904 As Long = 1462
Dim bClear As Boolean
Dim rArea As Range
Dim rCell As Range

Application.EnableEvents = False
For Each rArea In ChangedCells.Areas
For Each rCell In rArea
With rCell
bClear = ClearWhenEmpty And IsEmpty(.Value)
With .Offset(RowOffset, ColOffset)
If bClear Then
.ClearContents
Else
.NumberFormat = DTFormat
.Value = Date * -IncludeDate - _
Time * IncludeTime + _
n1904 * .Parent.Parent.Date1904
End If
End With
End With
Next rCell
Next rArea
Application.EnableEvents = True
End Sub

I copied this to a module but it wont show up when i try to recall it using
alt+f8 or by trying to attach it to a button? How do i use this?

 

[Dave Peterson]

You can use a macro that calls this macro:

Option Explicit
Sub Testme()
Call DateTimeStamp(ChangedCells:=ActiveCell, _
IncludeDate:=True, _
IncludeTime:=True, _
DTFormat:="dd mmm yyyy hh:mm", _
RowOffset:=0&, _
ColOffset:=0&, _
ClearWhenEmpty:=True)
End Sub

 

[jrm]

Thanks so much! One more thing, can you program this into the cell so that i
wont have to keep on pressing the button i created?

 

[Dave Peterson]

If you want the time in a cell, you can use:
ctrl-; (control semicolon)

if you want the time, use:
ctrl-: (control colon)

if you want both the date and time:
ctrl-; (spacebar) ctrl-:

=======
Or you could assign the macro a shortcut button.
tools|macro|macros
Select the testme (actually rename that to something nice) macro
click on the options button
and assign your favorite shortcut key combination.

 

[dwinmac]

I've been away form VBA for a few years. I don't remember ever seeing the
constructs below. That is:
Date * -IncludeDate-
Time* IncludeTime
n1904 * .Parent.Parent.Date1904

I understand the concatenation of several functions into the varaible Value
but the * - booleans are not familiar at all. Also I don't understand what
is happening here:
n1904 * rCell.Offset(RowOffset, ColOffset).Parent.Parent.Date1904

Can someone explain these construct or point me to a document that explains
them.

Thanks

 

[Dave Peterson]

You snipped the part of the message that showed that that was part of the
original poster's question. I didn't write that code.

But in VBA, if you convert the boolean True to a number:

Dim myB As Boolean
myB = True
MsgBox CLng(myB)

You'll see -1.

So -myB will coerce the boolean to a number and then negate it (resulting in
positive 1). False will be 0.

And there is a space character between the -includedate and trailing minus.
That minus is the subtraction operator.

You also snipped the preceding "with statement". That refered to a cell in a
range.

So cell.parent.parent.date1904
the parent of the cell is the worksheet.
the parent of the worksheet is the workbook.

So that boolean expression (.parent.parent.date1904) will be coerced to a number
(0 if the workbook is using 1900 as the base year, and -1 if the workbook is
using 1904 as the base year).

I couldn't find where that last expression was used.

It was more like:
rcell.Offset(RowOffset, ColOffset).value _
= Date * -IncludeDate - _
Time * IncludeTime + _
n1904 * .Parent.Parent.Date1904


The left hand side just points to a cell and the right handside is a lot of
arithmetic.