hi there,
I would like to be able to select a cell and type a number in it from 1-9.
When the number is entered i would like excel to record the time in that
cell. ie if i type 5 in cell B9 it automatically records the current time ie
10:57 in B9. Note the 5 is replaced with the recorded time. This time also
has to be fixed and cannot be changed, when i click on the next cell. This is
to record start and finish times in sheet.

Does it really matter what the number is that you enter?
try this Worksheet Event Code
Right Click on the Sheet Tab and select View Code.
Paste this code there.
Whenever you change a cell in the range A1:B10, the Code will change
the cell into the current time

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then Target =
Time
End Sub

You should disable events in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
Target =Time
End If
Application.EnableEvents = True
End Sub

Otherwise, the code will change a cell, which triggers _Change, which
changes a cell, which triggers _Change, which changes a cell, which
triggers _Change and on and on until VBA terminates the loop when it
runs out of stack space.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 7 Feb 2009 02:30:36 -0800 (PST), CurlyDave
wrote:

Does it really matter what the number is that you enter?
try this Worksheet Event Code
Right Click on the Sheet Tab and select View Code.
Paste this code there.
Whenever you change a cell in the range A1:B10, the Code will change
the cell into the current time

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then Target =
Time
End Sub

On Feb 7, 10:45*am, Chip Pearson wrote:
You should disable events in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
* * * * Target =Time
End If
Application.EnableEvents = True
End Sub

Otherwise, the code will change a cell, which triggers _Change, which
changes a cell, which triggers _Change, which changes a cell, which
triggers _Change and on and on until VBA terminates the loop when it
runs out of stack space.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
* * Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com

That makes total sense, interestingly that annoying loop does not
happen on my worksheet