Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lock a stamp date/time formula for an entry on a row?
I have a spreadsheet that is setup with Stamp Date/Time formula. I want each
row to be independent but every time I add an entry and save it, and move to the next row and add another entry it changes the Stamp Date/Time formula on all rows. How do I make it a true Stamp Date/Time spreadsheet so each row is independent of the other? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lock a stamp date/time formula for an entry on a row?
Only by using VBA event code in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. When you enter data in any cell in column A, a static date/time stamp goes into column B on same row. Gord Dibben MS Excel MVP On Wed, 2 Aug 2006 16:32:01 -0700, PROPERTIES INC. <PROPERTIES wrote: I have a spreadsheet that is setup with Stamp Date/Time formula. I want each row to be independent but every time I add an entry and save it, and move to the next row and add another entry it changes the Stamp Date/Time formula on all rows. How do I make it a true Stamp Date/Time spreadsheet so each row is independent of the other? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lock a stamp date/time formula for an entry on a row?
Thanks Gord but I am a newby to Excel. How do I use VBA? I am currently
using Excel 2002. So I am still learning the in's and out's of Excel. "Gord Dibben" wrote: Only by using VBA event code in the worksheet. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. When you enter data in any cell in column A, a static date/time stamp goes into column B on same row. Gord Dibben MS Excel MVP On Wed, 2 Aug 2006 16:32:01 -0700, PROPERTIES INC. <PROPERTIES wrote: I have a spreadsheet that is setup with Stamp Date/Time formula. I want each row to be independent but every time I add an entry and save it, and move to the next row and add another entry it changes the Stamp Date/Time formula on all rows. How do I make it a true Stamp Date/Time spreadsheet so each row is independent of the other? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lock a stamp date/time formula for an entry on a row?
Please read the part about "Right-click on the sheet tab" and onward.
The actual code to paste starts at Private Worksheet and goes to End Sub Gord On Thu, 3 Aug 2006 16:23:02 -0700, PROPERTIES INC. wrote: Thanks Gord but I am a newby to Excel. How do I use VBA? I am currently using Excel 2002. So I am still learning the in's and out's of Excel. "Gord Dibben" wrote: Only by using VBA event code in the worksheet. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. When you enter data in any cell in column A, a static date/time stamp goes into column B on same row. Gord Dibben MS Excel MVP On Wed, 2 Aug 2006 16:32:01 -0700, PROPERTIES INC. <PROPERTIES wrote: I have a spreadsheet that is setup with Stamp Date/Time formula. I want each row to be independent but every time I add an entry and save it, and move to the next row and add another entry it changes the Stamp Date/Time formula on all rows. How do I make it a true Stamp Date/Time spreadsheet so each row is independent of the other? Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i lock a cell? to save formula or function in it? | Excel Discussion (Misc queries) | |||
Formula and Data Entry in a Single Cell | Excel Discussion (Misc queries) | |||
date/time stamp | Excel Worksheet Functions | |||
lock formula in excel | Excel Discussion (Misc queries) | |||
Preserve Excel formula entry | Excel Discussion (Misc queries) |