Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default Fixing date of entry


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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default Fixing date of entry

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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default Fixing date of entry



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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

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.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

That's two double posts in the past two days.

Not sure what I'm doing.

On Mon, 09 May 2011 20:16:03 -0700, Gord Dibben wrote:

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.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default Fixing date of entry

In article , Gord Dibben
writes
Good to hear.

Gord


Hi

Thanks for your help with this.

I see the code works on columns A & B. Would it be an easy thing to
modify it so that it works across the other rows in the worksheet?
Ideally I'm trying to have 12 months worth across.

Effectively this would mean columns A ,C , E , G etc individually
displaying the date when text is entered to B , D , F . H ....

Could the code be amended to fit this condition?

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


Grateful for any assistance.
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

I'll have a try later this afternoon when my round of golf is complete.

Unless someone else leaps in before I get back(wink, wink..nudge, nudge)


Gord

On Wed, 11 May 2011 01:57:05 +0100, Colin Hayes
wrote:

In article , Gord Dibben
writes
Good to hear.

Gord


Hi

Thanks for your help with this.

I see the code works on columns A & B. Would it be an easy thing to
modify it so that it works across the other rows in the worksheet?
Ideally I'm trying to have 12 months worth across.

Effectively this would mean columns A ,C , E , G etc individually
displaying the date when text is entered to B , D , F . H ....

Could the code be amended to fit this condition?

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


Grateful for any assistance.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default Fixing date of entry

In article , Gord Dibben
writes
I'll have a try later this afternoon when my round of golf is complete.

Unless someone else leaps in before I get back(wink, wink..nudge, nudge)


Gord


Hi Gord

OK thanks - that would be great.

I was wondering if changes within the same the same row might change all
the cells with earlier data in the row to the same date. Or could they
be protected so as not too interfere with previous entries on the same
row.?


Grateful for your help.



On Wed, 11 May 2011 01:57:05 +0100, Colin Hayes

wrote:

In article , Gord Dibben
writes
Good to hear.

Gord


Hi

Thanks for your help with this.

I see the code works on columns A & B. Would it be an easy thing to
modify it so that it works across the other rows in the worksheet?
Ideally I'm trying to have 12 months worth across.

Effectively this would mean columns A ,C , E , G etc individually
displaying the date when text is entered to B , D , F . H ....

Could the code be amended to fit this condition?

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


Grateful for any assistance.


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

Revised code..........note only 6 columns.........you can fill in the rest<g

There is probably a better method of defining the range but this works.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Intersect(Range(Target(1).Address), _
Range("B:B, D:D, F:F, H:H, J:J, L:L")) _
Is Nothing Then GoTo enditall
With Target
If .Value < "" Then
With .Offset(0, -1)
.Value = Date
.Columns.AutoFit
End With
Else: .Offset(0, -1).Value = ""
End If
End With
enditall:
Application.EnableEvents = True
End Sub


Gord


On Wed, 11 May 2011 06:54:56 -0700, Gord Dibben wrote:

I'll have a try later this afternoon when my round of golf is complete.

Unless someone else leaps in before I get back(wink, wink..nudge, nudge)


Gord

On Wed, 11 May 2011 01:57:05 +0100, Colin Hayes
wrote:

In article , Gord Dibben
writes
Good to hear.

Gord


Hi

Thanks for your help with this.

I see the code works on columns A & B. Would it be an easy thing to
modify it so that it works across the other rows in the worksheet?
Ideally I'm trying to have 12 months worth across.

Effectively this would mean columns A ,C , E , G etc individually
displaying the date when text is entered to B , D , F . H ....

Could the code be amended to fit this condition?

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


Grateful for any assistance.

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

See my other posting for code.

Previous entries in same row are not affected by new entries.

Only operation that can remove or change a date is to re-enter or delete the
value in the target cell as we coded in second attempt.


Gord

On Wed, 11 May 2011 20:36:32 +0100, Colin Hayes
wrote:

I was wondering if changes within the same the same row might change all
the cells with earlier data in the row to the same date. Or could they
be protected so as not too interfere with previous entries on the same
row.?

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 465
Default Fixing date of entry

In article , Gord Dibben
writes
Revised code..........note only 6 columns.........you can fill in the rest<g

There is probably a better method of defining the range but this works.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Intersect(Range(Target(1).Address), _
Range("B:B, D:D, F:F, H:H, J:J, L:L")) _
Is Nothing Then GoTo enditall
With Target
If .Value < "" Then
With .Offset(0, -1)
.Value = Date
.Columns.AutoFit
End With
Else: .Offset(0, -1).Value = ""
End If
End With
enditall:
Application.EnableEvents = True
End Sub


Gord




Hi Gord

OK thanks again for this. It's working perfectly first time.

Grateful for your time and expertise.

Best Wishes


  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Fixing date of entry

Happy to assist.

Gord

On Thu, 12 May 2011 01:33:50 +0100, Colin Hayes
wrote:

Hi Gord

OK thanks again for this. It's working perfectly first time.

Grateful for your time and expertise.

Best Wishes

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofill date on date entry in a separate column lemonshark Excel Worksheet Functions 2 February 18th 10 06:11 PM
Fixing the date format in a macro or VBA Geoff B Excel Worksheet Functions 1 September 7th 09 11:00 AM
Excel 2002 date entry: Cannot get away from d-m-yy entry format Steve Eklund Excel Discussion (Misc queries) 3 May 11th 09 04:57 PM
Restricting date entry relative to current date bwalshjr Excel Worksheet Functions 1 April 10th 07 12:49 PM
Fixing the date. cs2883 New Users to Excel 7 October 7th 05 07:40 PM


All times are GMT +1. The time now is 12:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"