Archive of Mr Excel Message Board

On the Worksheet that you want the Date Stamp to be
entered, go down to the Sheet Tab (bottom left corner)
and right-click. Select "View Code". This will open
the VBA editor. This screen should have a couple or a
few windows open. The window you're looking for is
called the Project window, (probably the top left
window.) If it's not there for some reason click View
| Project explorer. In this window there is a
collapsable file tree. You're looking for something
that says VBA PROJECT and then the name of your
worksheet, or something to that effect. In this tree
there is selection called "This workbook." Double
click that.

Now this opens a vba editor that "sits" on top of the
entire workbook. The editor should be the screen on
your right. On this screen there are two drop-down
lists. One should say "General" and the other should
say "Declarations." Change the left one to "Workbook"
and change the right one to "BeforeSave." This will
drop a few lines of code in the editing window. Just
delete those.

Now copy and paste this bit of code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
Range("B1").Value = Format(Now(), "dd-mm-yy hh:mm:ss")

End Sub

This will place the date/time stamp in Cell B1. You
can change that to whatever you want, of course.
Also, you can change the way the date appears. Just
change the format in the code. For instance, if you
want to just have the date and not the time, delete
the hh:mm:ss part.