AutoClose Macro 
Author Message
 AutoClose Macro

This is really irritating.  I'm trying to create an AutoClose macro in
Excel 97, and it's just not working.  I've done this in earlier versions
of Excel.  I've tried each of the following:

Sub AutoClose()
Sub Auto_Close()
Private Sub AutoClose()
Private Sub Auto_Close()
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, _
        Cancel As Boolean)

The macro is located in the ThisWorkbook module.  I'm not getting any
syntax errors.  It's like Excel is just not recognizing that the macro
even exists.  To make things even more frustrating, I can't seem to find
any relevant information in the on-line Help.

Any help you offer will be greatly appreciated.

--

Please reply to the newsgroup so that all may learn
from your wisdom.

--Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro
On Sat, 29 Apr 2000 02:54:06 +1000, "Stormin' Norm"

Quote:

>Hi Thomas

>Try Private Sub Workbook_BeforeClose(Cancel As Boolean)

>It goes in the This Workbook Module

You can also just go to the code of the ThisWorkbook and select the
event you want to trap with the drop-down box in the top right corner
of the code window.

HTH

James Shoffit, Irving, TX



Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro
Hi Thomas,

I tried on XL 97 what you have described.  Same results. May be Auto_Close
 ) is archiac.
I called Auto_Close ( ) in another sub and it gets executed but the workbook
remains open.

Rishipa
All reply to this NG
============================================================



Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro
Hi Thomas

Try Private Sub Workbook_BeforeClose(Cancel As Boolean)

It goes in the This Workbook Module

HTH

Quote:
> This is really irritating.  I'm trying to create an AutoClose macro in
> Excel 97, and it's just not working.  I've done this in earlier versions
> of Excel.  I've tried each of the following:

> Sub AutoClose()
> Sub Auto_Close()
> Private Sub AutoClose()
> Private Sub Auto_Close()
> Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, _
>         Cancel As Boolean)

> The macro is located in the ThisWorkbook module.  I'm not getting any
> syntax errors.  It's like Excel is just not recognizing that the macro
> even exists.  To make things even more frustrating, I can't seem to find
> any relevant information in the on-line Help.

> Any help you offer will be greatly appreciated.

> --

> Please reply to the newsgroup so that all may learn
> from your wisdom.

> --Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro
Auto_Close is still supported in Excel97 and 2000.  Auto_Close does not
close the workbook.  It is automatically called by Excel when the workbook
is closed (manually, but not via VBA), but simply calling it from another
macro won't close the workbook.  It is called as a consequence of closing
the workbook -- it does not itself close the workbook.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services


Quote:
> Hi Thomas,

> I tried on XL 97 what you have described.  Same results. May be Auto_Close
>  ) is archiac.
> I called Auto_Close ( ) in another sub and it gets executed but the
workbook
> remains open.

> Rishipa
> All reply to this NG
> ============================================================



Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro

Quote:
> The macro is located in the ThisWorkbook module.  I'm not getting any
> syntax errors.  It's like Excel is just not recognizing that the macro

The Auto_Close has to be in a standard module (Insert->Module) to be
triggered, and not in the ThisWorkbook module.

--

Cheers

Iwer




Quote:
> This is really irritating.  I'm trying to create an AutoClose macro in
> Excel 97, and it's just not working.  I've done this in earlier versions
> of Excel.  I've tried each of the following:

<snip>


Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro

says...

Quote:
> Hi Thomas

> Try Private Sub Workbook_BeforeClose(Cancel As Boolean)

> It goes in the This Workbook Module

That works.  Thanks for your help.
--

Please reply to the newsgroup so that all may learn
from your wisdom.

--Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro

says...

Quote:
> On Sat, 29 Apr 2000 02:54:06 +1000, "Stormin' Norm"

> >Hi Thomas

> >Try Private Sub Workbook_BeforeClose(Cancel As Boolean)

> >It goes in the This Workbook Module

> You can also just go to the code of the ThisWorkbook and select the
> event you want to trap with the drop-down box in the top right corner
> of the code window.

> HTH

> James Shoffit, Irving, TX

Thanks for the tip.  I didn't know that before.  This will definitely
make it easier to resolve some of these types of issues myself, rather
than needing to go to the newsgroup.  However, there is one small detail
that you forgot to mention.  Once you open the This Document module, the
right side of the screen will have 2 drop-down boxes at the top.  If the
one on the left shows "General" then the drop-down box on the right will
only show you the existing subroutine names in the module.  You need to
change the drop-down box on the left so that it shows "Workbook", then
the drop-down box on the right will show all the stuff you're talking
about.
--

Please reply to the newsgroup so that all may learn
from your wisdom.

--Tom



Wed, 18 Jun 1902 08:00:00 GMT  
 AutoClose Macro
Pardon me for my incomplete and wrong representation of results of calling
Auto_Close( ).
Here is what should have been reported.

I tried on XL 97 what you have described.  Same results. May be Auto_Close
 ) is archiac.
I called Auto_Close ( ) in another sub and it gets executed AND the workbook
remains open.
If I do not call Auto_Close( ) in CallerOfAutoClose( ) then on closing the
workbook Auto_Close ( ) is not called by any of XL97's deeply private subs
because I did not see the MsgBox statement that I wrote in Auto_Close ( ).
XL 97 closes the workbook apparently ignoring MsgBox( ) in Auto_Close. But
when I call Auto_Close in  CallerOfAutoClose( ) obviously enough I see the
execution of the MsgBox function.  The behavior of XL 97 is strange but
coincides with observations of Thomas.

Regards

Rishipa
All reply to this NG
============================================================



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Autoclose Macro

2. autoclose macro: another question re: dates

3. AutoExit and AutoClose Macros

4. AutoClose macro

5. AutoClose Macro

6. Cancel closing a document from the autoclose-macro?

7. Intercepting Close event through AutoClose macro ( also intercepting FilePrint and FileSave)

8. Make AutoClose macro name a document from a bookmarked field.

9. Auto run macro when launching excel file and then autoclose excel file

10. Opposite of AUTOEXEC macro (ie AUTOCLOSE) ???


 
Powered by phpBB® Forum Software © phpBB Group