Auto Insert the present time when a cell is filled with a data,

[Dinanath]

"Insert Time when a cell is filled with data, but don't update the time
how to use the following code and where to put this code can someone please
explain a step by step guide for this please?
Once the time is inserted, you could edit/copy/paste special/values.
You might also try setting up a macro that would do the now function and
then paste values.

The following code will auto insert the time using ctrl+y


Sub Time_Constant()
'
' Time_Constant Macro
'
' Keyboard Shortcut: Ctrl+y
'
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A1").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub"

 

[Sheeloo]

Open Excel
Press ALT-F11 to open VB Editor
Choose Insert->Module
Paste the code
Switch to Excel
Choose Tools->Macros
Select the Time_Constant macro
Click on Options
Choose the shortt-cut key you want...

Use that key to insert current date/time with format in the current cell
"[$-409]h:mm AM/PM;@"

 

[Gord Dibben]

If you are going to use shortcut key to insert a staic time you may as well
go with the built-in shortcut.

CTRL + SHIFT + ;(semi-colon)

I would use a change event to insert the static time when the dat is entered
and not bother with any shortcut key macro.

This code added to the sheet module will give you a time in column A when
data is entered in column B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, -1).Value = Now()
End If
End Sub

Right-click on the sheet tab and "View Code". Copy/paste into that module.

Edit to suit..............if you need help with that, post back with details
on range(s) to use.


Gord Dibben MS Excel MVP