Hey all,

My boss asked me to come up with a spreadsheet that logs the type of
computer problem we've had and what we did to fix. He also wants each entry
to be dated in automatically, to make things easier. So, I tried using the
NOW() forumla in the following way:

=IF(ISBLANK(A2),"",NOW())

This would immediatly paste the current date if anyone entered in a name
entry. The only problem I ran into was that the NOW() function continually
updates, even after the entry is put in. So all of my data entries end up
looking like the same date.

My question: Is there a way to do this with the date remaining static, like
a stamp almost, when it's entered?

Thanks! I appreciate any help you guys and girls can provide.

-Tinker

Tinker,

Here is some VBA code that will enter today's date in any cell selected
within A1:A100

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
.Value = Date
.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.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tinker" wrote in message
...
Hey all,

My boss asked me to come up with a spreadsheet that logs the type of
computer problem we've had and what we did to fix. He also wants each
entry
to be dated in automatically, to make things easier. So, I tried using the
NOW() forumla in the following way:

=IF(ISBLANK(A2),"",NOW())

This would immediatly paste the current date if anyone entered in a name
entry. The only problem I ran into was that the NOW() function continually
updates, even after the entry is put in. So all of my data entries end up
looking like the same date.

My question: Is there a way to do this with the date remaining static,
like
a stamp almost, when it's entered?

Thanks! I appreciate any help you guys and girls can provide.

-Tinker

Hey Bob,

Thank you very much for the coding! I'm taking a Visual Basic course next
semester so hopefully I'll be able to start writing my own soon.

In reguards to your code, it worked beautifuly...except it doesn't give the
current time. It gives the current date, which is awesome, but I need the
current time.

Thank you very much!

-Tinker

Perhaps this alteration?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
.Value = Format(Now, "dd mm yyyy hh:mm:ss")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP

On Mon, 27 Dec 2004 11:43:08 -0800, Tinker
wrote:

Hey Bob,

Thank you very much for the coding! I'm taking a Visual Basic course next
semester so hopefully I'll be able to start writing my own soon.

In reguards to your code, it worked beautifuly...except it doesn't give the
current time. It gives the current date, which is awesome, but I need the
current time.

Thank you very much!

-Tinker

Bob - I was excited to find your post of this code. Can you show me how to
modify the code to be "conditional". I would like to test for any numeric
value0 in a cell, if True, then auto enter a static date into another cell.

Ex. I enter 2.3 into cell B1 and automatically A1 shows the current date
(this date of course needs to "freeze" to the day it was created and not
increment any following day)

Thanks,
B.T.

"Bob Phillips" wrote:

Tinker,

Here is some VBA code that will enter today's date in any cell selected
within A1:A100

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
.Value = Date
.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.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tinker" wrote in message
...
Hey all,

My boss asked me to come up with a spreadsheet that logs the type of
computer problem we've had and what we did to fix. He also wants each
entry
to be dated in automatically, to make things easier. So, I tried using the
NOW() forumla in the following way:

=IF(ISBLANK(A2),"",NOW())

This would immediatly paste the current date if anyone entered in a name
entry. The only problem I ran into was that the NOW() function continually
updates, even after the entry is put in. So all of my data entries end up
looking like the same date.

My question: Is there a way to do this with the date remaining static,
like
a stamp almost, when it's entered?

Thanks! I appreciate any help you guys and girls can provide.

-Tinker

Since you're doing the typing yourself, you could use the
worksheet_change event.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("b1")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:

Application.EnableEvents = False
With Target
If IsNumeric(.Value) Then
If .Value 0 Then
With .Offset(0, -1)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Else
'do something else???
End If
Else
'do a different something else?
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



R.Toshack wrote:

Bob - I was excited to find your post of this code. Can you show me how to
modify the code to be "conditional". I would like to test for any numeric
value0 in a cell, if True, then auto enter a static date into another cell.

Ex. I enter 2.3 into cell B1 and automatically A1 shows the current date
(this date of course needs to "freeze" to the day it was created and not
increment any following day)

Thanks,
B.T.

"Bob Phillips" wrote:

Tinker,

Here is some VBA code that will enter today's date in any cell selected
within A1:A100

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
.Value = Date
.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.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tinker" wrote in message
...
Hey all,

My boss asked me to come up with a spreadsheet that logs the type of
computer problem we've had and what we did to fix. He also wants each
entry
to be dated in automatically, to make things easier. So, I tried using the
NOW() forumla in the following way:

=IF(ISBLANK(A2),"",NOW())

This would immediatly paste the current date if anyone entered in a name
entry. The only problem I ran into was that the NOW() function continually
updates, even after the entry is put in. So all of my data entries end up
looking like the same date.

My question: Is there a way to do this with the date remaining static,
like
a stamp almost, when it's entered?

Thanks! I appreciate any help you guys and girls can provide.

-Tinker




--

Dave Peterson

Works like a charm - Thanks! B.T.

"Dave Peterson" wrote:

Since you're doing the typing yourself, you could use the
worksheet_change event.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("b1")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:

Application.EnableEvents = False
With Target
If IsNumeric(.Value) Then
If .Value 0 Then
With .Offset(0, -1)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Else
'do something else???
End If
Else
'do a different something else?
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



R.Toshack wrote:

Bob - I was excited to find your post of this code. Can you show me how to
modify the code to be "conditional". I would like to test for any numeric
value0 in a cell, if True, then auto enter a static date into another cell.

Ex. I enter 2.3 into cell B1 and automatically A1 shows the current date
(this date of course needs to "freeze" to the day it was created and not
increment any following day)

Thanks,
B.T.

"Bob Phillips" wrote:

Tinker,

Here is some VBA code that will enter today's date in any cell selected
within A1:A100

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
.Value = Date
.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.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tinker" wrote in message
...
Hey all,

My boss asked me to come up with a spreadsheet that logs the type of
computer problem we've had and what we did to fix. He also wants each
entry
to be dated in automatically, to make things easier. So, I tried using the
NOW() forumla in the following way:

=IF(ISBLANK(A2),"",NOW())

This would immediatly paste the current date if anyone entered in a name
entry. The only problem I ran into was that the NOW() function continually
updates, even after the entry is put in. So all of my data entries end up
looking like the same date.

My question: Is there a way to do this with the date remaining static,
like
a stamp almost, when it's entered?

Thanks! I appreciate any help you guys and girls can provide.

-Tinker




--

Dave Peterson