Workbook closing

[Bob]

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

 

[Chip Pearson]

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)

 

[JLGWhiz]

It has a before close event. I am not sure how you would capture it for an
add-in.
Here is the Help file description.

BeforeClose Event
SpecificsOccurs before the workbook closes. If the workbook has been
changed, this event occurs before the user is asked to save changes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Cancel False when the event occurs. If the event procedure sets this
argument to True, the close operation stops and the workbook is left open.

 

[Bob]

Thanks guys for your great inputs. That is true. I am not sure how to
handle the save? For example, if the user decides to close the file, and I
delete all the information before he gets the message "whether he wants to
save the changes", and then the user decides to cancel the close, I loose
all the information. I guess, the only thing I can do is the following
(which I have no idea on how to even start on)
1- If the user decides to close the file, the program gives him the option
to save
2- if he chooses Cancel, for my code not to do anything.
3- if he chooses Yes the program should save his work, then for my program
to delete the custom properties, and then save the workbook again (to get
rid of them), and then close the workbook.
4- if the user chooses No, I have no idea what to do. If, since the opening
of the workbook, the workbook was not saved, then this is easy, the workbook
should close, and the custom properties are deleted. That is fine.
However, if the workbook was saved at some point, my custom properties are
also saved with it. Then the user does some work, and decides to abandon
the changes and close the file without saving. In this case, I have no idea
how to delete my custom properties.

Bob

 

[Bob]

Thanks Chip. Your code helped. However, I have put some comment on it in
my reply to JLGWhiz. thanks for your help.

Bob