I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

To enter the date/time in column C whenever data is entered in column B

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
ws_exit:
Application.EnableEvents = True
End Sub

'For one cell use
'If Target.Address = "$B$3" Then

'For a range use
'If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.

Adjust to suit or post back with some more specifics about where you want the
date entered.


Gord Dibben MS Excel MVP

On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms.
wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

That worked great. If I want to do the same thing to columns F, I and L can
I use the "or" and copy and this statement "Application.Intersect(Target,
Columns("B:B"))"? Thanks so much!!

"Gord Dibben" wrote:

To enter the date/time in column C whenever data is entered in column B

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
ws_exit:
Application.EnableEvents = True
End Sub

'For one cell use
'If Target.Address = "$B$3" Then

'For a range use
'If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.

Adjust to suit or post back with some more specifics about where you want the
date entered.


Gord Dibben MS Excel MVP

On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms.
wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

Try this one.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
If Intersect(Range(Target(1).Address), _
Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
ws_exit:
Application.EnableEvents = True
End Sub


Gord

On Mon, 12 Feb 2007 06:40:02 -0800, Ms. Beasley
wrote:

That worked great. If I want to do the same thing to columns F, I and L can
I use the "or" and copy and this statement "Application.Intersect(Target,
Columns("B:B"))"? Thanks so much!!

"Gord Dibben" wrote:

To enter the date/time in column C whenever data is entered in column B

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
ws_exit:
Application.EnableEvents = True
End Sub

'For one cell use
'If Target.Address = "$B$3" Then

'For a range use
'If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.

Adjust to suit or post back with some more specifics about where you want the
date entered.


Gord Dibben MS Excel MVP

On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms.
wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

This version may be better.

Checks for blank cells.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
If Intersect(Range(Target(1).Address), _
Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(0, 1).Value = Now()
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord

On Mon, 12 Feb 2007 10:27:29 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Try this one.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
If Intersect(Range(Target(1).Address), _
Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
ws_exit:
Application.EnableEvents = True
End Sub


Gord

On Mon, 12 Feb 2007 06:40:02 -0800, Ms. Beasley
wrote:

That worked great. If I want to do the same thing to columns F, I and L can
I use the "or" and copy and this statement "Application.Intersect(Target,
Columns("B:B"))"? Thanks so much!!

"Gord Dibben" wrote:

To enter the date/time in column C whenever data is entered in column B

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
ws_exit:
Application.EnableEvents = True
End Sub

'For one cell use
'If Target.Address = "$B$3" Then

'For a range use
'If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.

Adjust to suit or post back with some more specifics about where you want the
date entered.


Gord Dibben MS Excel MVP

On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms.
wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

What I've been asked to do is create a shared log sheet that will allow users
to click in a blank cell that will automatically populate today's date and
then lock the cell. Then tomorrow when it is opened it will still show the
date of yesterday's entry into the log sheet. I am not proficient at Excel
at all!!! Thanks so much!!

"Ms. Beasley" wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

Ms Beasley

Many features are unavailable in Shared Workbooks.

See Help on "shared workbook" for a list.

Changing protection/locking/unlocking cell is one of these features not
available.

What you want to do can easily be done on a non-shared workbook using event
code.


Gord

On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley
wrote:

What I've been asked to do is create a shared log sheet that will allow users
to click in a blank cell that will automatically populate today's date and
then lock the cell. Then tomorrow when it is opened it will still show the
date of yesterday's entry into the log sheet. I am not proficient at Excel
at all!!! Thanks so much!!

"Ms. Beasley" wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

Can you walk me through that? Thanks!

"Gord Dibben" wrote:

Ms Beasley

Many features are unavailable in Shared Workbooks.

See Help on "shared workbook" for a list.

Changing protection/locking/unlocking cell is one of these features not
available.

What you want to do can easily be done on a non-shared workbook using event
code.


Gord

On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley
wrote:

What I've been asked to do is create a shared log sheet that will allow users
to click in a blank cell that will automatically populate today's date and
then lock the cell. Then tomorrow when it is opened it will still show the
date of yesterday's entry into the log sheet. I am not proficient at Excel
at all!!! Thanks so much!!

"Ms. Beasley" wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

Through what?

The event code for a non-shared worksheet?

NOTE: will not work on a Shared Workbook as I said earlier.

1. Select column A and any other range you want unlocked.
2. FormatCellsProtection uncheck the "locked" box.
3. Widen column A to about 16 minimum.

Select the sheet tab and "View Code". Copy/paste this code into that sheet
module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
ActiveSheet.Unprotect Password:="justme"
For Each cell In Target
If cell.Value = "" Then
With cell
.Value = Now
.Locked = True
End With
End If
Next
Cancel = True
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

Double-click on any blank cell in column A and get the date/time entered and
cell locked so timestamp cannot be changed.

Does not affect protection on other unlocked cells.

"justme" can be any password you choose.

You will of course want to lock the code from prying eyes so..........

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-clickVBAProject
PropertiesProtectionLock for Viewing. Apply a password and save/close the
workbook.

When re-opened, code will be unviewable or uneditable.


Gord

On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley
wrote:

Can you walk me through that? Thanks!

"Gord Dibben" wrote:

Ms Beasley

Many features are unavailable in Shared Workbooks.

See Help on "shared workbook" for a list.

Changing protection/locking/unlocking cell is one of these features not
available.

What you want to do can easily be done on a non-shared workbook using event
code.


Gord

On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley
wrote:

What I've been asked to do is create a shared log sheet that will allow users
to click in a blank cell that will automatically populate today's date and
then lock the cell. Then tomorrow when it is opened it will still show the
date of yesterday's entry into the log sheet. I am not proficient at Excel
at all!!! Thanks so much!!

"Ms. Beasley" wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!

I think this is exactly what I needed. Thanks a bunch!!

"Gord Dibben" wrote:

Through what?

The event code for a non-shared worksheet?

NOTE: will not work on a Shared Workbook as I said earlier.

1. Select column A and any other range you want unlocked.
2. FormatCellsProtection uncheck the "locked" box.
3. Widen column A to about 16 minimum.

Select the sheet tab and "View Code". Copy/paste this code into that sheet
module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
ActiveSheet.Unprotect Password:="justme"
For Each cell In Target
If cell.Value = "" Then
With cell
.Value = Now
.Locked = True
End With
End If
Next
Cancel = True
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

Double-click on any blank cell in column A and get the date/time entered and
cell locked so timestamp cannot be changed.

Does not affect protection on other unlocked cells.

"justme" can be any password you choose.

You will of course want to lock the code from prying eyes so..........

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-clickVBAProject
PropertiesProtectionLock for Viewing. Apply a password and save/close the
workbook.

When re-opened, code will be unviewable or uneditable.


Gord

On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley
wrote:

Can you walk me through that? Thanks!

"Gord Dibben" wrote:

Ms Beasley

Many features are unavailable in Shared Workbooks.

See Help on "shared workbook" for a list.

Changing protection/locking/unlocking cell is one of these features not
available.

What you want to do can easily be done on a non-shared workbook using event
code.


Gord

On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley
wrote:

What I've been asked to do is create a shared log sheet that will allow users
to click in a blank cell that will automatically populate today's date and
then lock the cell. Then tomorrow when it is opened it will still show the
date of yesterday's entry into the log sheet. I am not proficient at Excel
at all!!! Thanks so much!!

"Ms. Beasley" wrote:

I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow. Can
anybody help me with this? Thanks!