Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    inhibit the execution of Auto_ macro?

    How is it possible to inhibit the execution of Auto_ macros when opening a document in Word97/VBA?

    I can find nothing in the help files to tell me.

    I've tried the old "On Error Resume Next" trick to see if that will cater for the occasional errors that an Auto_ macro generates, but to no avail.


    <pre> Documents.Open FileName:=strFile
    </pre>


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: inhibit the execution of Auto_ macro?

    Pressing shift as you open the file should inhibit any auto run macros - it does in Excel.

    Andrew C

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inhibit the execution of Auto_ macro?

    Andrew, thanks for this, but I need to do it via program code.

    I have a WQord97/VBA application that opens all the documents/templates. There'll be noone around to hold down the Shift key.

    FWIW I tried holding down the shift key while recording a macro, but I see nothing there, either, that will inhibit the Auto action.


    What's even stranger is that yesterday's serach of the MSKnowledgebase using "Inhibit" and "Suppress" in conjunction with "Auto" offered no help.

    The options to open with suppression of macros ("Disable Macros") won't be much use to me, because I want to inspect the macro code, and "Disable" makes that code unreadable.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: inhibit the execution of Auto_ macro?

    Would something like the following suffice
    <pre>On Error Goto ErrHandler</pre>

    Your code
    <pre>ErrHandler:
    Exit Sub</pre>

    Putting a Stop statement in your code would throw you into the VB editor.

    Andrew

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: inhibit the execution of Auto_ macro?

    Hi Chris,
    I've never actually used this but allegedly
    WordBasic.DisableAutoMacros
    should do what you're after.
    Please let me know if it does work.
    PS If your userpics are anything to go by, Yilgarn would appear to be at the forefront of plastic surgery technology!
    PPS In what sense does disabling macros make the code unreadable? I do it all the time with spreadsheets that people send me to make sure they're safe (and because I'm nosy)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inhibit the execution of Auto_ macro?

    > Andrew Cronnolly replied to your post on the VB / VBA board at the Woody's Lounge On Error Goto ErrHandler

    Thanks, Andrew. despiote the fact that I hate "On error" Rory came up with a WordBasic solution.


    Come to think of it, that's even worse! (grin!)

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inhibit the execution of Auto_ macro?

    > WordBasic.DisableAutoMacros


    Superb!


    How odd that the Help files suggest Wordbasic as the route to take, and that there's no VBA equivalent. I suppose if you're planning to drop support of something .....

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: inhibit the execution of Auto_ macro?

    Hi Rory
    <hr>PPS In what sense does disabling macros make the code unreadable? I do it all the time with spreadsheets that people send me to make sure they're safe <hr>
    I have been Googling around trying to find an Excel equivalent to WordBasic.DisableAutoMacros 0 and WordBasic.DisableAutoMacros 1.
    From your post I suspect that you have found an equivalent command for Excel. Did I get lucky?
    Regards
    Don

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inhibit the execution of Auto_ macro?

    Rory is not online at the moment. I think he simply meant that he has macro security set to Medium, and clicks "Disable macros" in the warning that appears when he opens a workbook that he receives from others. It is still possible to view code in the VB Editor if macros are disabled. You just can't run them.

    Remark: if you want to enable macros, but prevent automatic macros from running, hold down Shift while opening the workbook.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: inhibit the execution of Auto_ macro?

    Thanks Hans
    I have come to believe that by default, when opening or closing a workbook with VBA code, the Auto_Open and Auto_Close macros are inhibited. However, they can be fired with Run "Insert Filename Here!Insert Module Name Here.Auto_Open" for example.
    Please comment.
    Regards
    Don

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inhibit the execution of Auto_ macro?

    This is a rather confusing subject. There are two bits of code that can be executed automatically when a workbook is opened:

    1) An event procedure named Workbook_Open in the ThisWorkbook module.
    2) A macro named Auto_Open in a standard module.

    If you open the workbook interactively and enable macros, both 1) and 2) will be executed automatically, in this order.
    If you open the workbook using code, only 1) will be executed automatically. You can run 2) by issuing

    ActiveWorkbook.RunAutoMacros Which:=xlAutoOpen

    Instead of ActiveWorkbook, you can also use a variable of type Workbook.

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: inhibit the execution of Auto_ macro?

    Thank you Hans
    Regards
    Don

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: inhibit the execution of Auto_ macro?

    Hi Don,
    Hans was, as usual, spot on with his reply. The only thing to add, which you are probably already aware of, is that you can prevent the Workbook_Open event from firing by setting <code>Application.EnableEvents = False</code> before opening a workbook in code and then setting it to True afterwards.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: inhibit the execution of Auto_ macro?

    Thank you Rory
    Regards
    Don

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    ... Application.EnableEvents = False before opening a workbook ...
    The attached ZIP file contains two Excel2000 workbooks and a Word2000 document.

    Auto.xls is a small Excel workbook that displays a msgbox for the Workbook_Open and Auto-Open events/macros.
    XLAutoNot.xls is a small Excel workbook whose macro "XLAutoNot" can open Auto.xls and inhibit both Auto macros.
    AutoNOT.doc is a small Word project whose macro "testOpen" attempts to duplicate the feats of XlAutoNot.xls.

    It seems to me that .EnableEvents = False functions as expected when invoked from Excel 2000, but fails to inhibit the WorkBook-Open event when called from Word2000.

    I tested this on my WinXp/office 2003 system and it works as I would expect and hope.

    The disabled line "xlApp.AutomationSecurity" is not available in office 2000

    There may be a bug in office 2000, but I'd appreciate hearing from anyone who is still running a 2000 system.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •