Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill date on date entry in a separate column | Excel Worksheet Functions | |||
Fixing the date format in a macro or VBA | Excel Worksheet Functions | |||
Excel 2002 date entry: Cannot get away from d-m-yy entry format | Excel Discussion (Misc queries) | |||
Restricting date entry relative to current date | Excel Worksheet Functions | |||
Fixing the date. | New Users to Excel |