Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    Oops!

    I have created a workbook with a macro that automatically fires upon opening the workbook. The macro does it's thing, then quits Excel. Excel security is set to medium, which would always produce the macro security dialog box, where I could disable macros if I didn't want it to fire. Well, I created a digital certificate (using selfcert) and signed the macro.

    Now when I open the workbook, the macro runs and closes Excel automatically (like I wanted it to). However, the old "hold shift key while opening" trick doesn't do it's, huh, trick. It doesn't keep the macro from firing. I do not seem to have access to make any changes to this workbook. This would be bad.

    Someone please tell me that there's a simple thing I've overlooked <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    On a related note, is there a command line argument that will allow me to run a macro at startup (besides the Workbook_Open procedure)? Access has such a feature, but couldn't find anything similar in Excel.

  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: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    the old "hold shift key while opening" trick should work for you. I just tested.

    However if you continue to have problems, you could switch off event processing with Application.EnableEvents = False, then open your workbook, and set event processing back on with True in place of False.

    If you name a sub Auto_Open and place it in a general module, it should run automatically when the workbook is opened.

    Andrew C

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    Some clarification:
    Subs named Auto_open are not run if the file is opened via code: you have to use the RunAutoOpen method to run them after the workbook is opened (or before the workbook is closed via code if you have an Auto_Close macro.

    To run auto open:
    <pre>Workbooks.Open "c:MyFile.xls"
    ActiveWorkbook.RunAutoMacros xlAutoOpen</pre>


    To run autoclose
    <pre>With ActiveWorkbook
    .RunAutoMacros xlAutoClose
    .Close
    End With</pre>


    The Workbook open and close events will always fire (unless you have disabled events) whether the file is opened manually or via a macro.

    Steve

  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: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    I had assumed that the workbook was being opened manually, as < old "hold shift key while opening" trick > was being employed.

    I am aware that Auto_open only fires automatically when the workbook is manually opened.

    Andrew C

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    Sorry, I did not mean to imply that you didn't know. I just wanted to clarify for the other readers who might not know since there is confusion on this occasionally.

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    AFAIK Excel does not have any startup switch that allows you to run macro code at startup, you have to use the Workbook Open event. For info on available startup command line arguments, see this MSKB article:

    XL2000: Startup Switches for Microsoft Excel 2000

    If you look at the available options, the one that may be most useful is the /s switch to open Excel in "Safe Mode." If you use shortcut with this option, you still get the "Disable Macros/Enable Macros" warning, even if the file's VBA project has been signed using a self-signed certificate. You can then elect to enable or disable macros. Only drawback, after responding to the warning msg, you have to respond to an additional msgbox asking you to enter your Office User Name & Initials. See MSKB article for more info on this option.

    Another option I tested was the /p switch that allows you to specify the active path to use instead of the default path. You can create shortcut with this option, specifying path other than default (typically the current user's My Documents folder), then have the Workbook Open event test to see if the current folder is not the default, using the Windows GetCurrentDirectory API function to get directory path for the current process, and the Excel Application DefaultFilePath property to get the default path. This worked OK, as long as Excel not already open. Otherwise not reliable, because the current path could change for any number of reasons, such as browsing for a file to open or save.

    Recommend try using /s switch as simplest approach. Also the shift key bypass worked when opening workbook from Open File dialog in Excel; did not work when double-clicking a shortcut to open file.

    HTH

  7. #7
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    Thanks, everyone, for your responses to my question. Digitally signing a project has been tricky to figure out, having not done so to this point. This was just another piece to the puzzle.

    I have been double-clicking a shortcut to open the file. Opening it through the Open dialog box while holding down shift worked as designed. I will probably add a simple IF Statement and MsgBox asking if I want to run the macro, which I can comment out once all my testing is done.

    Thanks again for everyone's help.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    I usually have a public constant, like:

    Public bTest as Boolean=True

    WHich I use to execute or bypass code that is only there for testing. One example I use sometimes is:

    Activesheet.Printout Preview:=bTest

    So that whilst I'm testing, I just get a preview, not a print.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot disable macros after digital signing (Excel 2002 (xp) SP2)

    What a great tip. Thanks Jan!

Posting Permissions

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