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
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.
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
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.
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
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
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
Sam – how does one convert an XLS file to an EXE file?
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
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
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.
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
Just filed a bug with MSFT on Excel 2007: holding the shift key does NOT prevent the auromacros from running!
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)
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
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
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
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.
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.
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…