Disabling Macros on Startup

To open a file so that automatic macros (e.g. Workbook_Open and Auto_Open) aren’t executed, you have a couple of options. First, if your macro security is set to medium, you should get a dialog like this

DisableMacros1

You can select the Disable Macros button and the autoexecuting macros won’t execute. Using this option will disable macros so that you can’t even run them after the file is open. That can be useful, but if you just want to prevent the autoexecuting macros from firing on open, it’s like putting out a candle with a fire hose.

To prevent autoexecuting macros from firing, but still be able to run them, open the workbook while holding down the SHIFT key.

This method is useful if you have a bug in your autoexecuting macro, particularly when the macro closes Excel before you’ve had a chance to fix it.

12 Comments

  1. ron:

    Hi all

    >open the workbook while holding down the SHIFT key.

    Note : This is not working if you open the file by double clicking on it(folder or desktop).

    Use File>Open in Excel

  2. Rob Bovey:

    It’s also worth noting that this won’t work with add-in workbooks. They require a VBA solution like the one I provide in my Excel Utilties.

  3. Zoltan Till:

    If you need to disable bypass then you can set the confidential sheets to xlSheetVeryHidden on the workbook_save event and set to xlSheetVisible on the auto_open event.

    You can apply a more level security by user authentication in the auto_open event.

    Zoltan Till
    Hungary

  4. hesham:

    Hi there

    How can i Disable the Shift Key at startupuse ?
    Is there a vba code to do so, if yes could u please send it to me?

    Thank u very much for your help

    Yours

  5. sam:

    To open a file so that macros are always enabled no matter what the security setting- convert the xls file to an exe file
    Sam

  6. fzz:

    Sam - how does one convert an XLS file to an EXE file?

  7. sam:

    fzz- Use ExceltoExe - Download it for free from Orlando’s site : http://cpap.com.br/orlando/
    It gives you an exe file which works just like an excel file….but macros always stay enabled..even when the security setting is high…
    It is basically creating an exe wrapper to the Excel file and opening a temp copy of the original excel file in the back groung…. but I have no idea how he managed to make that work…

    Sam

  8. Rahul:

    I have an Excel file containing vb codes.And I also have many confiential data in the file which I dont want user to see. I have locked those with vb code. But if the user disables the macros my purpose of locking the data is getting defeted.
    I would like to know whether there is a code which will detect whether macros are enabled are disables and if are disables then the file shouild not open.
    Thanks

  9. Tushar Mehta:

    Rahul: If code execution is disabled, how do you expect code to detect if it is disabled? But, more important, I doubt that there is anyone, in or outside of Microsoft, who would consider Excel as a secure tool for anything. Well, maybe the versions support IRM but I don’t know enough about that. Other than that, If you have confidential data don’t put them in Excel.

  10. Mike Alexander:

    Rahul,

    Check out this video that demonstrates how to force users to enable macros
    http://www.datapigtechnologies.com/flashfiles/forcemacros.html

    To see a real file in action, download Andrew’s Mastermind game. It forces you to enable macros or it won’t open.
    http://www.andrewsexceltips.com/Mastermind.zip

  11. jkpieterse:

    Just filed a bug with MSFT on Excel 2007: holding the shift key does NOT prevent the auromacros from running!

  12. Maaron:

    I’m using automation Excel, i created small program for just reading desired cells,
    everyhting works fine, until i use excel file with macro, which is password protected
    and when i tried to open this excel file with my program, error message appears:
    ‘Compile error in hidden module: xxx_mod’
    and next message:
    ‘The xxx.dll is not present….’

    (i suppose Excel tries to initiliaze macro and looks for dll)
    but i set HIGH security level for running macros, so i expect, macro should not run

    anyway, my program stops on this error and not continue until user action, what is not my expectation

    is there any way how to prevent displaying any error message during opening file (which requires user action)
    or how i can open such file without ‘Compile error’?
    (if i open it manually, it opens without any error - if i set high level for macro)

Leave a comment