MS Excel insert current time incl. seconds on click 1

[Fae]

Scenario - Swimming team times
I need to be able to either click on an Excel cell and it must insert the current time (including seconds - as the seconds are what really count here)(maybe vb for excel) and then it must jump down to the next cell. So, either by clicking the cell or one just to the left of it (check box) or a button located somewhere on the spread sheet.
Please help - Your input is valued

 

[xlbo]

2 suggestions:

1. Utilise the worksheet_Selection Change event

To do this, right click on the sheet tab and choose "view Code". The selection change event is the default event and an empty sub will be created automatically. enter the following within that sub:

target.value = format(Now(),"hh:mm:ss&quot

2. Have a button attached to the following code

sub EnterTime()
nRow = cells(65536,activecell.column).end(xlup).row
cells(nRow+1,activecell.column).value = format(Now(),"hh:mm:ss&quot
end sub Rgds
Geoff

[blue]Si hoc legere scis, nimis eruditionis habes[/blue]

 

[Fae]

Geoff, thanks a million - It works great - Now just one little problem - every cell i click on it puts in the time - I only need it to be activated in the B-Range e.g. B2 till B3000

 

[xlbo]

Change code to this:

If target.column = 2 and target.row < 3000 and target.row >1 then
target.value = format(Now(),&quot;hh:mm:ss&quot
else
end if

watch for word wrap - 1st line should go from If > then Rgds
Geoff

[blue]Si hoc legere scis, nimis eruditionis habes[/blue]

 

[xlbo]

Just as an extra note to this - you can go down to 100's of a second by creating a custom format

Select B2:B3000

format>Cells
Select Number tab
Select Custom
Enter hh:mm:ss.00
in the text box
Your time entry will now be accurate to 1/100th of a second Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Fae]

Thanks a lot Geoff, it works great - much appreciated

Fae
Miracles we do immediately ...
imposibilities take a little longer.