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.

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.

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.

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.