Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem
Each time I open my worksheet, I make a new entry on the next empty row, I
want to have the date fill in on column D of the current row. I have tried the NOW() and TODAY() together with IF(ISBLANK), but all previously entered dates update each time I re-open the sheet. My question; is there a way to enter a date in a cell that will not update each time I re-open the workbook, but rather go to the next row in that column and enter a new date. I hope I have explained that clear enough, I searched several groups and only found 1 similar question with a single VB answer which was nonfunctional according to a reply. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem
Search for "System Date/Time into a Cell or within Code" on David
McRitchie's WebPage for insight into how to handle this issue. http://www.mvps.org/dmcritchie/excel/datetime.htm HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- On Sat, 27 Sep 2003 15:04:56 GMT, "M. lane" wrote: My question; is there a way to enter a date in a cell that will not update each time I re-open the workbook, but rather go to the next row in that column and enter a new date. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem
If I understand you right try this, it will put the date in the next row
down in column D when the workbook is opened Private Sub Workbook_Open() Range("D65536").End(xlUp).Offset(1, 0).Value = Date End Sub To put in this macro, from your workbook right-click the workbook's icon and pick View Code. This icon is to the left of the "File" menu this will open the VBA editor, in the left hand window double click on thisworkbook, under your workbook name, and paste the code in the window that opens on the right hand side, press Alt and Q to close this window and go back to your workbook, now this will run every time you open the workbook. If you are using excel 2000 or newer you may have to change the macro security settings to get the macro to run. -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "M. lane" wrote in message .. . Each time I open my worksheet, I make a new entry on the next empty row, I want to have the date fill in on column D of the current row. I have tried the NOW() and TODAY() together with IF(ISBLANK), but all previously entered dates update each time I re-open the sheet. My question; is there a way to enter a date in a cell that will not update each time I re-open the workbook, but rather go to the next row in that column and enter a new date. I hope I have explained that clear enough, I searched several groups and only found 1 similar question with a single VB answer which was nonfunctional according to a reply. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem
We solved a similar problem by writing a VB routine that
uses an event called WorkbookBeforeSave. We were trying to automatically insert a date stamp the moment an adjacent cell took on a particular value for future reference w/o having this date get updated every time the workbook was re-opened (as happens with now() or today()) and this made it work. It will make certain the date does not change if the contents of the adjacent cell are still the same. -----Original Message----- Search for "System Date/Time into a Cell or within Code" on David McRitchie's WebPage for insight into how to handle this issue. http://www.mvps.org/dmcritchie/excel/datetime.htm HTH Paul ---------------------------------------------------------- ---------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. ---------------------------------------------------------- ---------------------------------------------------- On Sat, 27 Sep 2003 15:04:56 GMT, "M. lane" wrote: My question; is there a way to enter a date in a cell that will not update each time I re-open the workbook, but rather go to the next row in that column and enter a new date. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem
slight modification of Paul's code so you will be in the
correct row to enter you data. i = ActiveCell.SpecialCells(xlLastCell).Row + 1 Range("D" & i).Select Selection.Value = Now -----Original Message----- Each time I open my worksheet, I make a new entry on the next empty row, I want to have the date fill in on column D of the current row. I have tried the NOW() and TODAY() together with IF(ISBLANK), but all previously entered dates update each time I re-open the sheet. My question; is there a way to enter a date in a cell that will not update each time I re-open the workbook, but rather go to the next row in that column and enter a new date. I hope I have explained that clear enough, I searched several groups and only found 1 similar question with a single VB answer which was nonfunctional according to a reply. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem
I'm resending this reply since I noticed I answered to
someone who replied to you and not to you: We solved this with a small VB routine that uses an event called WorkbookBeforeSave. Make the routine write a date/time stamp on the target cell when the adjacent or referenced cell takes on a desired value (i.e: ISNOTBLANK, TRUE, FALSE, =1, etc.). WorkbookBeforeSave checks whether the contents have changed everytime you re-open the wkbook: if they are still the same the original date/stamp will not be changed(as happens with today() or now())and will remain for future reference. Works beautifully. If you'd like the actual VB routine, e-mail me. -----Original Message----- Each time I open my worksheet, I make a new entry on the next empty row, I want to have the date fill in on column D of the current row. I have tried the NOW() and TODAY() together with IF(ISBLANK), but all previously entered dates update each time I re-open the sheet. My question; is there a way to enter a date in a cell that will not update each time I re-open the workbook, but rather go to the next row in that column and enter a new date. I hope I have explained that clear enough, I searched several groups and only found 1 similar question with a single VB answer which was nonfunctional according to a reply. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem
Thanks for all the replies, I have what I need now.
wrote in message ... Search for "System Date/Time into a Cell or within Code" on David McRitchie's WebPage for insight into how to handle this issue. http://www.mvps.org/dmcritchie/excel/datetime.htm HTH Paul -------------------------------------------------------------------------- ------------------------------------ Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------- ------------------------------------ On Sat, 27 Sep 2003 15:04:56 GMT, "M. lane" wrote: My question; is there a way to enter a date in a cell that will not update each time I re-open the workbook, but rather go to the next row in that column and enter a new date. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date problem | New Users to Excel | |||
Exel increment date problem wrt todays date. | Excel Worksheet Functions | |||
Date problem | Excel Worksheet Functions | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
'Date' problem | Excel Discussion (Misc queries) |