XL2000: How to Create and Use a Workbook_BeforeClose Procedure

• Microsoft Excel 2000

SUMMARY

You can run a Microsoft Visual Basic for Applications macro (Sub procedure) every time a particular workbook is closed, by attaching the procedure to the BeforeClose event of the workbook. This article contains sample code that illustrates how to use the BeforeClose event.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Start the Visual Basic Editor (press ALT+F11)



2. On the View menu, click Project Explorer.



3. In the Project Explorer window, double-click the ThisWorkbook object.
   
   The module sheet for the ThisWorkbook object is opened.



4. Type the following code in the module sheet:




Private Sub Workbook_BeforeClose(Cancel As Boolean)

      ' Place the current date and time into cell A1 of Sheet1.
      Worksheets("sheet1").Range("a1").Value = _
         Format(Date + Time, "mm/dd/yy hh:mm")

      ' Save the current workbook.
      ThisWorkbook.Save
End Sub 

5. Switch to Microsoft Excel by pressing ALT+F11.



6. On the File menu, click Close.

REFERENCES

For more information about the BeforeClose event, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type BeforeClose Event in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: auto close autoclose XL2000

Keywords : kbprg kbdta kbdtacode OffVBA PgmHowto KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto