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?

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?

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?

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