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

see

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


In article ,
Kenny wrote:

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

thanks!


"JE McGimpsey" wrote:

see

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


In article ,
Kenny wrote:

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

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?



"JE McGimpsey" wrote:

see

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


In article ,
Kenny wrote:

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

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 wrote:

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?

"JE McGimpsey" wrote:

see

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


In article ,
Kenny wrote:

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


--

Dave Peterson

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" wrote:

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 wrote:

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?

"JE McGimpsey" wrote:

see

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


In article ,
Kenny wrote:

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


--

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.

jrm wrote:

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" wrote:

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 wrote:

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?

"JE McGimpsey" wrote:

see

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


In article ,
Kenny wrote:

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


--

Dave Peterson


--

Dave Peterson

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 he
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" wrote:

.Value = Date * -IncludeDate - _
Time * IncludeTime + _
n1904 * .Parent.Parent.Date1904

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.

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

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.

dwinmac wrote:

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 he
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" wrote:

.Value = Date * -IncludeDate - _
Time * IncludeTime + _
n1904 * .Parent.Parent.Date1904

--

Dave Peterson