Hi all

I have a small problem that I hope someone can help with.

In cell A1 I have this code :

=IF(B1=""," ",TODAY())

With this , if B4 is empty the cell is empty. If B4 has content then it
puts the date.

I replicate this down to A19. As I make entries in the cells in column B
, so the date of entry is recorded.

Unfortunately of course the date of entry is updated to the present date
when I open the workbook. How can I fix the date of entry so that it
remains at the date entry was originally made in column B?

Grateful for any assistance.

I would abandon the use of a formula which uses the function TODAY().

TODAY() is a volatile function so will not remain static.

To get a static date entered you can use VBA sheet event code.

Right-click on the INPUT sheet tab and "View Code"

Copy/paste the following code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Me.Range("B" & n).Value < "" Then
Me.Range("A" & n).Value = Date
End If
End If
enditall:
Application.EnableEvents = True
End Sub

If you do want to use the volatile TODAY() function, see John McGimpsey's site
for
a method using circular references.

http://www.mcgimpsey.com/excel/timestamp.html

Note also the VBA solution, which is similar to above event code.


Gord Dibben MS Excel MVP

On Mon, 9 May 2011 21:51:26 +0100, Colin Hayes wrote:


Hi all

I have a small problem that I hope someone can help with.

In cell A1 I have this code :

=IF(B1=""," ",TODAY())

With this , if B4 is empty the cell is empty. If B4 has content then it
puts the date.

I replicate this down to A19. As I make entries in the cells in column B
, so the date of entry is recorded.

Unfortunately of course the date of entry is updated to the present date
when I open the workbook. How can I fix the date of entry so that it
remains at the date entry was originally made in column B?

Grateful for any assistance.

In article , Gord Dibben
writes
I would abandon the use of a formula which uses the function TODAY().

TODAY() is a volatile function so will not remain static.

To get a static date entered you can use VBA sheet event code.

Right-click on the INPUT sheet tab and "View Code"

Copy/paste the following code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Me.Range("B" & n).Value < "" Then
Me.Range("A" & n).Value = Date
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Hi

OK thanks very much for that.

I have more than one sheet in the workbook , and assume I need to enter
the code under each tab. When I enter text into a cell in B1 , the date
appears in the cell A1 , which is perfect.

Out of interest , can the code be modified so that if subsequently the
text entered into B1 is now deleted , that the date in A1 would be
removed too?

Thanks again.

and assume I need to enter
the code under each tab

Incorrect assumption.

When you need the same code in multiple worksheets, you can place the code once
in Thisworkbook module and cover all sheets.

Put this revised code in Thisworkbook Module under Microsoft Excel Objects in
VBE

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
With ActiveSheet
If .Range("B" & n).Value < "" Then
.Range("A" & n).Value = Date
Else: .Range("A" & n).Value = ""
End If
End With
End If
enditall:
Application.EnableEvents = True
End Sub

Delete the previous code you copied to any worksheet module.


Gord

On Mon, 9 May 2011 23:00:20 +0100, Colin Hayes wrote:

In article , Gord Dibben
writes
I would abandon the use of a formula which uses the function TODAY().

TODAY() is a volatile function so will not remain static.

To get a static date entered you can use VBA sheet event code.

Right-click on the INPUT sheet tab and "View Code"

Copy/paste the following code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Me.Range("B" & n).Value < "" Then
Me.Range("A" & n).Value = Date
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Hi

OK thanks very much for that.

I have more than one sheet in the workbook , and assume I need to enter
the code under each tab. When I enter text into a cell in B1 , the date
appears in the cell A1 , which is perfect.

Out of interest , can the code be modified so that if subsequently the
text entered into B1 is now deleted , that the date in A1 would be
removed too?

Thanks again.

HI

OK thanks for your time and expertise. It's working perfectly.

Best Wishes ,


In article , Gord Dibben
writes
and assume I need to enter
the code under each tab

Incorrect assumption.

When you need the same code in multiple worksheets, you can place the code
once
in Thisworkbook module and cover all sheets.

Put this revised code in Thisworkbook Module under Microsoft Excel Objects in
VBE

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
With ActiveSheet
If .Range("B" & n).Value < "" Then
.Range("A" & n).Value = Date
Else: .Range("A" & n).Value = ""
End If
End With
End If
enditall:
Application.EnableEvents = True
End Sub

Delete the previous code you copied to any worksheet module.

Good to hear.

Gord

On Tue, 10 May 2011 01:47:45 +0100, Colin Hayes
wrote:



HI

OK thanks for your time and expertise. It's working perfectly.

Best Wishes ,


In article , Gord Dibben
writes
and assume I need to enter
the code under each tab

Incorrect assumption.

When you need the same code in multiple worksheets, you can place the code
once
in Thisworkbook module and cover all sheets.

Put this revised code in Thisworkbook Module under Microsoft Excel Objects in
VBE

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
With ActiveSheet
If .Range("B" & n).Value < "" Then
.Range("A" & n).Value = Date
Else: .Range("A" & n).Value = ""
End If
End With
End If
enditall:
Application.EnableEvents = True
End Sub

Delete the previous code you copied to any worksheet module.

Good to hear.

Gord

On Tue, 10 May 2011 01:47:45 +0100, Colin Hayes
wrote:



HI

OK thanks for your time and expertise. It's working perfectly.

Best Wishes ,


In article , Gord Dibben
writes
and assume I need to enter
the code under each tab

Incorrect assumption.

When you need the same code in multiple worksheets, you can place the code
once
in Thisworkbook module and cover all sheets.

Put this revised code in Thisworkbook Module under Microsoft Excel Objects in
VBE

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
With ActiveSheet
If .Range("B" & n).Value < "" Then
.Range("A" & n).Value = Date
Else: .Range("A" & n).Value = ""
End If
End With
End If
enditall:
Application.EnableEvents = True
End Sub

Delete the previous code you copied to any worksheet module.