Auto Insert the present time when a cell is filled with a data,
"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" |
Auto Insert the present time when a cell is filled with a data,
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;@" "Dinanath" wrote: "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" |
Auto Insert the present time when a cell is filled with a data,
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 On Sun, 8 Mar 2009 12:55:01 -0700, Dinanath wrote: "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" |
All times are GMT +1. The time now is 10:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com