Excel

Write Date Time Stamp on Sheet on Save.

Ease of Use

Easy

Version tested with

2003 

Submitted by:

sheeeng

Description:

The Excel workbook will put a date time stamp on the worksheet before save. 

Discussion:

The date time stamp will actually help user to remember when is the last time they save a workbook. 

Code:

instructions for use

			

Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Display a date using the short date format specified in your computer's _ regional settings. Range("A1").Value = "vbShortDate" Range("B1").Value = FormatDateTime(Now, vbShortDate) 'Display a time using the 24-hour format (hh:mm). Range("A2").Value = "vbShortTime" Range("B2").Value = FormatDateTime(Now, vbShortTime) 'Display a date using the long date format specified in your computer's regional _ settings. Range("A4").Value = "vbLongDate" Range("B4").Value = FormatDateTime(Now, vbLongDate) 'Display a time using the time format specified in your computer's regional settings. Range("A5").Value = "vbLongTime" Range("B5").Value = FormatDateTime(Now, vbLongTime) 'Display a date and/or time. If there is a date part, display it as a short date. _ If there Is a time part, display it As a Long time. If present, both parts are displayed. Range("A7").Value = "vbGeneralDate" Range("B7").Value = FormatDateTime(Now, vbGeneralDate) Columns("A:B").EntireColumn.AutoFit Range("A1").Select End Sub

How to use:

  1. From Excel go to Tools-Macros-Visual Basic Editor. (ALT+F11)
  2. In the Project Explorer (CTRL+R), click on the ThisWorkbook object
  3. Paste the code above into the code pane
  4. Close the Visual Basic Editor (File-Close)
  5. Save your file and close it.
 

Test the code:

  1. Save the workbook to view changes on the date time stamp.
 

Sample File:

Date Time Stamp.zip 7.5KB 

Approved by mdmackillop


This entry has been viewed 269 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express