Excel 2003 Inserting current date

M

mark

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.
 
G

Gord Dibben

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
 
M

mark

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
 
G

Gord Dibben

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
 
Top