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.

Posted in Uncategorized

18 thoughts on “Disabling Macros on Startup

  1. 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. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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.

  8. 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)

  9. Hi.

    I have a wookbook that freezes upon opening. I believe its to do with a line of code I entered (wrongly, probably!) I would like to know how to delete the code without actually opening the workbook, or disable the code upon startup (shift key doesn’t work),

    Cheers

    PHIL

  10. Phil, hopefully you have an answer by now, but if not I solved my similar problem by first opening up a different workbook, changing the macro security level to medium(tools/options/security-tab/macro security button),now open the corrupted workbook with macros disabled, now view the code (alt + F11)and look for your problem. Mine was that I had two macros with identical names (I copied and pasted code and forgot to change the name) and it somehow caused the file to corrupt when it was last closed.

    Good luck

    Ben

  11. i have made a sheet which contain a set of macros…
    Now i need a separte copy of sheet with No Macros ..
    Please suggest me what to do….It will be so appreciable..

    Thanks
    Regards
    Joe

  12. Try this.

    First, make a reference to “Microsoft Visual Basic For Applications Extensibility 5.3?

    Then put the following sub into a standard module:

    ‘————————————————
    Public Sub RemoveVBA(pSheetname As String)
        With Application.VBE.ActiveVBProject
            With .VBComponents(pSheetname).CodeModule
                .DeleteLines 1, .CountOfLines
            End With
        End With
    End Sub
    ‘————————————————

    Then, after the code that copies the sheet, call the above sub, passing the sheet name of the copy that you want to remove the code from.

  13. Whoops,

    Forgot to mention in that code snippet I posted above. . .

    1/ pSheetname is not the name as it appears on the sheet tab in the Excel Window. Instead, it is the VBA name shown in the Project Explorer Window in the VBA screen, which you can change in the Properties Window, also in the VBA screen.

    2/ If you use this at work, there may be a firewall that will disallow this code. The worst I experienced was a firewall deleting all the modules in a Workbook when I tried to save it. If you send an Excel file containing code that uses the reference to “Microsoft Visual Basic For Applications Extensibility 5.3? as an attachment to an email, the attachment may be stripped from the email be Outlook.

    It varies, but happily, 2007 gives you a way to say the code is trustworthy. Go to the Excel Options dialog box, by clicking on Office – Excel Options – Trust Center – Trust Center Settings, and under “Developer Macro Settings”, tick the checkbox labelled “Trust access to the VBA project object model”.

    Good luck.

    Nick.

  14. Hi.

    I need your help, i need to disable an userform from other workbook, the userform is shown with a Private Sub in “Thisworbook”:

    Private Sub Workbook_Open()

    My macro stops when useform appear, so i’ve to close it for continue the procedure…

    I hope that you can help me.

    Ooooo, another thing, can i to copy modules with macros?

    Tks…

Leave a Reply

Your email address will not be published. Required fields are marked *