I'm currently working with excel in combination with a special security
program called eInfotree. The security-shell provides security-audits etc
which are required due to regulations applicable in our industry. Now, when
we use an autofilter in excel, and close the excel-file whith the filter on
(meaning only a limited number of fields displayed) the file in combination
with the security causes the file to become corrupt.

We know it is an option to include a VBA-macro inside each excel-file, which
resets the autofilter before closing it, but since we don't want to touch all
the inidivudal files, we were looking into the option to include such a macro
in the xlstart-macro.
So the question; is it possible to create a macro on application level
(independent of each loaded workbook) that can trigger certain actions when
closing an individual workbook? In this case we want it to reset the
autofilter before closing the file.

Any feedback is welcomed.

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
On Error Resume Next
Wb.Worksheets(1).AutoFilter
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tygox1" wrote in message
...
I'm currently working with excel in combination with a special security
program called eInfotree. The security-shell provides security-audits etc
which are required due to regulations applicable in our industry. Now,
when
we use an autofilter in excel, and close the excel-file whith the filter
on
(meaning only a limited number of fields displayed) the file in
combination
with the security causes the file to become corrupt.

We know it is an option to include a VBA-macro inside each excel-file,
which
resets the autofilter before closing it, but since we don't want to touch
all
the inidivudal files, we were looking into the option to include such a
macro
in the xlstart-macro.
So the question; is it possible to create a macro on application level
(independent of each loaded workbook) that can trigger certain actions
when
closing an individual workbook? In this case we want it to reset the
autofilter before closing the file.

Any feedback is welcomed.

Bob,

thank you for this reply.
Unfortunately it does not seems to work. Did you foresee to include this
code in each individual sheet, or in the auto-open one?

Regards,
Tim

"Bob Phillips" wrote:

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
On Error Resume Next
Wb.Worksheets(1).AutoFilter
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tygox1" wrote in message
...
I'm currently working with excel in combination with a special security
program called eInfotree. The security-shell provides security-audits etc
which are required due to regulations applicable in our industry. Now,
when
we use an autofilter in excel, and close the excel-file whith the filter
on
(meaning only a limited number of fields displayed) the file in
combination
with the security causes the file to become corrupt.

We know it is an option to include a VBA-macro inside each excel-file,
which
resets the autofilter before closing it, but since we don't want to touch
all
the inidivudal files, we were looking into the option to include such a
macro
in the xlstart-macro.
So the question; is it possible to create a macro on application level
(independent of each loaded workbook) that can trigger certain actions
when
closing an individual workbook? In this case we want it to reset the
autofilter before closing the file.

Any feedback is welcomed.

Combine Bob's code with the guidelines here :
http://www.cpearson.com/excel/AppEvent.htm

NickHK

"Tygox1" wrote in message
...
Bob,

thank you for this reply.
Unfortunately it does not seems to work. Did you foresee to include this
code in each individual sheet, or in the auto-open one?

Regards,
Tim

"Bob Phillips" wrote:

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
On Error Resume Next
Wb.Worksheets(1).AutoFilter
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tygox1" wrote in message
...
I'm currently working with excel in combination with a special
security
program called eInfotree. The security-shell provides security-audits
etc
which are required due to regulations applicable in our industry.
Now,
when
we use an autofilter in excel, and close the excel-file whith the
filter
on
(meaning only a limited number of fields displayed) the file in
combination
with the security causes the file to become corrupt.

We know it is an option to include a VBA-macro inside each excel-file,
which
resets the autofilter before closing it, but since we don't want to
touch
all
the inidivudal files, we were looking into the option to include such
a
macro
in the xlstart-macro.
So the question; is it possible to create a macro on application level
(independent of each loaded workbook) that can trigger certain actions
when
closing an individual workbook? In this case we want it to reset the
autofilter before closing the file.

Any feedback is welcomed.