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?

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
[email protected]> 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?
>

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
> [email protected]> 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?
> >
>
>

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.
<[email protected]> 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
>> [email protected]> 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

This worked great. The only thing I am challenged with is when I enter data in Column A and Column B is auto-populated, it does not lock the cell automatically. I have programmed the sheet to do this but the cell has to be physically typed in for that function to work. How can I make this function auto lock the cell after it automatically populates with Date/time stamp. Also how can I make it show a.m. / p.m. and regular time instead of military time? Thank you so much for your help.

Hello Hillster, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.