Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel 2003 Inserting current date
I wish to create a spreadsheet that whenever data is entered in a
row/column, the actual date is entered automatically in a date column. I do not want the date to be overridden. |
#2
|
|||
|
|||
Mark
Right-click your sheet tab and "View Code". Copy/paste this event code in that sheet module. Enter anything in any cell in column A and B will get a static date. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Col B time will not change if data in Col A is edited On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" _ And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy") End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Tue, 8 Feb 2005 11:05:03 -0800, "mark" wrote: I wish to create a spreadsheet that whenever data is entered in a row/column, the actual date is entered automatically in a date column. I do not want the date to be overridden. |
#3
|
|||
|
|||
Gord
Many Thanks How can I now protect the range of cells with date in it? When I try to protect the column the formula disappears. Mark "Gord Dibben" wrote: Mark Right-click your sheet tab and "View Code". Copy/paste this event code in that sheet module. Enter anything in any cell in column A and B will get a static date. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Col B time will not change if data in Col A is edited On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" _ And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy") End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Tue, 8 Feb 2005 11:05:03 -0800, "mark" wrote: I wish to create a spreadsheet that whenever data is entered in a row/column, the actual date is entered automatically in a date column. I do not want the date to be overridden. |
#4
|
|||
|
|||
Mark
One more step........ Copy/paste the code below into the ThisWorkbook module. Right-click on the Excel logo left of "File" on menu bar or on logo at top left corner of Window if not maximized. Select "View Code" and paste into that module. Private Sub Workbook_Open() Dim sh As Worksheet Application.ScreenUpdating = False Sheets("Sheet1").Protect "justme", , , userinterfaceonly:=True Application.ScreenUpdating = True End Sub "Sheet1" is the name of your sheet. Adjust to suit. What this code does is allow the code to run on a protected sheet. Unlock column A so's data can be entered. Lock Column B so's the dates cannot be deleted. Save the workbook then re-open. Gord On Wed, 9 Feb 2005 01:37:04 -0800, "mark" wrote: Gord Many Thanks How can I now protect the range of cells with date in it? When I try to protect the column the formula disappears. Mark "Gord Dibben" wrote: Mark Right-click your sheet tab and "View Code". Copy/paste this event code in that sheet module. Enter anything in any cell in column A and B will get a static date. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Col B time will not change if data in Col A is edited On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" _ And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy") End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Tue, 8 Feb 2005 11:05:03 -0800, "mark" wrote: I wish to create a spreadsheet that whenever data is entered in a row/column, the actual date is entered automatically in a date column. I do not want the date to be overridden. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel 2003 is changing my date formats | Excel Worksheet Functions | |||
Inserting the date that an excel sheet is saved into a cell | Excel Discussion (Misc queries) | |||
In Excel 2003, entering date without slashes, the date is incorre. | Excel Discussion (Misc queries) | |||
Excel 2003 "File Open": how keep folders at top with sort by Date. | Excel Discussion (Misc queries) |