Put the following code in the ThisWorkbook module (it must be in the
ThisWorkbook module):

Private WithEvents XLApp As Excel.Application

Private Sub Workbook_Open()
Set XLApp = Application
End Sub

Private Sub XLApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Dim WS As Worksheet
Dim N As Long
For Each WS In Wb.Worksheets
With WS.CustomProperties
For N = .Count To 1 Step -1
.Item(N).Delete
Next N
End With
Next WS

End Sub


With this code, whenever any workbook is closed, all of the custom
properties are removed from each worksheet in the workbook.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)



On Tue, 23 Sep 2008 15:51:36 -0700, "Bob" wrote:

Hi all:

In excel 2003, I am setting some CustomProperties for the active worksheet.
However, before the workbook is closed, I would like to delete all the
custom properties. Does anyone know how I can find out when the workbook is
closing, and in which event I have to write my code for. Please note,
ultimately, my code would become an Add-in, and would not be tied to any
workbook in particular. Thanks for your help.

Bob