Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Prevent Open Event
Deze pagina in het Nederlands

Preventing auto_open and Workbook_Open events from running

Introduction

As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running. This little article shows you how to achieve that.

Why would you want to do this? Typically, I use this on my own files in which I am still developing. The Open event might contain code that takes a while to run, or configures the project in a way which I don't want to happen when I start working with the file.

If the file you opened using the methods described below contains other event handlers, note that these event handlers will remain disabled until you start a macro manually (by clicking a command button or a menu entry) or if a User Defined function in your code has been called.

I do not recommend using this method to open files from sources you do not know or trust. The VBA code can contain events which will eventually run and possibly cause trouble.

Excel 2010, 2013, 2016

Macro security set to low or trusted document

If you have set your macro security to "Enable All Macros" or you have already set the document to trusted or the document is in a trusted folder, click File, Open, select your file and hold down the shift key when you click the Open button:

Selecting the file from the File, Open dialog

Fig. 1: Selecting the file from the File, Open dialog (Excel 2010, 2013, 2016)

Of course your file might be listed in the Most Recently Used files (MRU) list.  In that case, hold shift when you click the file in the list:

Clicking the file on the MRU

Fig. 2: Clicking the file on the MRU (Excel 2010)

Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.

Macro security set to prompt

If your document is not in a trusted folder, has never been set to be trusted and macro security is set to something other than enable all macros to run, you can repeat what I've shown above.

Normally, if you open such a document, Excel will show the security bar. In this situation however, due to you holding down shift, Excel shows this window:

Enable macros dialog

fig 3: Enable macros dialog (Excel 2010)

Because you held down the shift button when you clicked the file in the MRU or when you clicked the Open button, you can now just click "Enable macros" and no Automacros will run.

Excel 2007 and up

Macro security set to low or trusted document

Note that this seems to fail if the trusted folder is on a network share!

If you have set your macro security to "Enable All Macros" or you have already set the document to trusted or the document is in a trusted folder, click File, Open, select your file and hold down the shift key when you click the Open button:

Selecting the file from the File, Open dialog

Fig. 4: Selecting the file from the File, Open dialog (Excel 2007 and up)

Of course your file might be listed in the Most Recently Used files (MRU) list.  In that case, hold shift when you click the file in the list:

Clicking the file on the MRU

Fig. 2: Clicking the file on the MRU (Excel 2007 and  up)

Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.

Macro security set to prompt

If your document is not in a trusted folder, has never been set to be trusted and macro security is set to something other than enable all macros to run, you can repeat what I've shown above.

Normally, if you open such a document, Excel will show the security bar. In this situation however, due to you holding down shift, Excel shows this window:

Enable macros dialog

fig 3: Enable macros dialog (Excel 2007 and up)

Because you held down the shift button when you clicked the file in the MRU or when you clicked the Open button, you can now just click "Enable macros" and no Automacros will run. 

Excel 97, 2000, XP, 2003

Macro security set to low or trusted document

In case your macro security is set to low, or your VBA code is signed and you have set the publisher as trusted, you must hold down the shift key when you click the Open button on the File, Open dialog:

Opening the file from the File, Open dialog

Fig. 7: Opening the file from the File, Open dialog (Excel 97-2003)

Of course your file might be listed in the Most Recently Used files (MRU) list.  In that case, hold shift when you click the file in the list:

Clicking the file on the MRU

Fig. 8: Clicking the file on the MRU (Excel 97-2003)

Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.

Macro security set to medium or higher

If your macro security is set to al least "Medium" and you have never set its publisher to trusted (for a signed macro) you can forget about holding shift when opening the file or clicking the file on the MRU list. Instead, you must hold shift when you enable macros:

Hold shift when you press Enable macros

Fig.9: Hold shift when you press Enable macros

Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.

 


Comments

All comments about this page:


Comment by: Jon Peltier (12/7/2009 11:47:19 AM)

What bothers me is that closing the Security Warning using the X in the top right corner in Excel 2003 and earlier doesn't even open the file. Doing the same in 2007 opens the file with macros disabled. I liked the option not to open the file at all if I discover that it contains macros.

 


Comment by: Mischa (2/5/2010 2:56:21 PM)

This does not work in Excel 2007 if the macro workbook is in one of your TrustedLocations.

 


Comment by: Jan Karel Pieterse (2/6/2010 6:18:45 AM)

Hi Mischa,

Thanks, I'll double-check if you are correct.

 


Comment by: Jan Karel Pieterse (2/10/2010 3:51:02 AM)

Hi Mischa,

I just checked and it DOES work for a trusted folder, provided you open the file from within Excel and follow the instructions shown above!

 


Comment by: Ashish (3/4/2010 7:10:37 AM)

I am having trouble debugging the Workbook_open() method in Excel 2007.

The dilema is as below :

When I open the workbook with Macros disabled, this is to set a break point, It won't let me run any Macros.

If I open the workbook with Macros enabled, the code runs OK but I can not debug since the break point was not set.

Any suggestions, pls email me

 


Comment by: Jan Karel Pieterse (3/4/2010 7:25:07 AM)

Hi Ashish,

You can open the file and enable macros, using the shift key trick shown in this article to prevent the open event from firing.
Then go into the VBE, locate the Workbook_Open sub and hit F8 to start debugging.

 


Comment by: Dominick (6/1/2010 9:26:51 AM)

Macro 4.0 programs (written in the 90's) that have Auto_Open named to the first cell of code is NOT disabled by holding down the shift key when openning. Do you have any other suggestions to prevent the macro from firing off when the workbook is openned? Thanks.

 


Comment by: Dominick (6/1/2010 9:31:06 AM)

Sorry. I didn't mention my working environments. Disabling Auto_Open macros using the Shift key worked fine up until Excel 2007. Please advise. Thank you.

 


Comment by: Jan Karel Pieterse (6/2/2010 12:41:58 AM)

Hi Dominick,

I could not prevent Excel 2007, nor Excel 2010, from running the XLM Open event either. A bug if you ask me!

 


Comment by: Paul Olsavsky (7/5/2010 7:47:10 PM)

I've tried all the suggestions, and I still can't stop autoopen macros from running by holding down "Shift". Is it posible that our crack network people have made some policy entry in the standard Office platform that they permit to run in our work environment?

Regards,

Paul O.

 


Comment by: Jan Karel Pieterse (7/6/2010 12:04:22 AM)

Hi Paul,

As far as I know, policies cannot affect the effect of the shift key, but you may prove me wrong of course.
Are you positive you held down the shift key at the right moment (press BEFORE clicking the Open button and keep holding it down all the way through)?

 


Comment by: Theo Gevers (7/8/2010 12:07:46 AM)

Is er al een oplossing omtrent het openen van excelsheets met een auto_open macro met behulp van het ingedrukt houden van de SHIFT toets? Bij werkt dit namelijk ook niet. Hoe kan ik deze functie herstellen?

 


Comment by: Jan Karel Pieterse (7/8/2010 11:17:34 AM)

Hi Theo,

Answering in English....
It appears there is a bug in Excel 2007, where in these circumstances the shift key does NOT work to prevent auto macros from running:

- File is in a trusted folder
- Folder is on a network

 


Comment by: Jeff Whale (7/20/2010 12:41:19 PM)

Sort of related question to Open events

I have a workbook that has 3 separate modules

The main one creates an output book by copying sheets as required

It also copies one of its modules into the new workbook by
WorkMacro = ActiveWorkbook.Path & "\Temporary Module Store.bas"
    
    ActiveWorkbook.VBProject.VBComponents("Module2") _
                .Export WorkMacro

then later
Set WorkObject = ActiveWorkbook.VBProject
    
    With WorkObject.VBComponents
        .Import WorkMacro
    End With
    
    Kill WorkMacro


This works fine but I'd like to also create an open and a close event macro in the output workbook and don't know how to do this

ASny tips please ?

 


Comment by: Jan Karel Pieterse (8/16/2010 2:56:27 AM)

Hi Jeff,

Here is what I would do:
- Create a template workbook with all code in place
- copy the sheets to that template workbook

 


Comment by: Jeff Whale (8/17/2010 1:59:12 PM)

Your reply was :-
Comment by: Jan Karel Pieterse (8/16/2010 2:56:27 AM)

Hi Jeff,

Here is what I would do:
- Create a template workbook with all code in place
- copy the sheets to that template workbook

Thanks Jan - yep, already thought that would work but the problem is that the template is run by many people at different locations and they'd all need a new template that would need to be kept up to date

Anyway, it's an interesting academic exrecise - if you happen to discover a way it would be really good

Thanks again

 


Comment by: Jan Karel Pieterse (8/18/2010 12:13:47 AM)

Hi Jeff,

True, distributing two files is more work than one.

But is that more cumbersome than telling all of the users they have to change their macro security settings to allow access to the VBA project?

 


Comment by: Jeff (1/4/2012 4:26:40 PM)

Thank you so much!

 


Comment by: Darrel L. Schrock (2/8/2012 6:10:17 AM)

To Jeff on the Multiple Users:
I have a lot of different Excel templates that are used by multiple users. To save having to keep more than one file updated, I have a file with an "Auto-Open" Macro that first copies the file from my stored location to the User's "C:\temp" folder, and then opens it for use (or what ever that second file is designed to do). This is my "Link" that users access. This allows many users and only one file to maintain. They don't actually open the original file, just the copy on their PC. This was just my thought on your issue. Thanks.

 


Comment by: Rob P (4/12/2012 9:08:59 AM)

To get around the issue of the shift key not working in Excel 2007 when you want to prevent trusted macros running without prompting you can use this method:

Open Excel
Press Alt+F11 to open the VBA window
Press Ctrl+G to go to the Immediate window
Type the following and press return:

Application.EnableEvents = False


Then open your workbook. Macros will be enabled but the one that fires on the workbook open event will not run automatically as events are disabled. Note that all events which can fire macros such as workbook save will also be disabled.

You should restart Excel afterwards so that events are re-enabled and your workbooks function in the way that you expect.

 


Comment by: Jan Karel Pieterse (4/13/2012 2:13:55 AM)

Hi Rob,

Thanks for the comments. But the shift key DOES work in Excel 2007, provided you hold it down at the right moment during the opening process, as stated above. Also, you don't have to restart Excel to enable events of course, you can simply change the False to True in your suggestion and hit enter again.

 


Comment by: Herbert (5/10/2012 10:22:22 PM)

Hi there, when I record a macro (Excel 2010) containing code to open a second document it does not record the file open action within the macro code. Is there perhaps some solution to this?

Many thanks

Herbert

 


Comment by: Jan Karel Pieterse (5/12/2012 10:24:54 AM)

Hi Herbert,

I don't know why that does not work for you, but the syntax for opening a file is quite straightforward:

Workbooks.Open "c:\users\jkp\MyExcelFile.xlsx"

 


Comment by: Dave Bonin (8/28/2012 4:35:31 PM)

I have a similar situation with an entirely different solution.

I have a monster workbook that grabs a user-specified raw data file and produces a finished report. Many people use this workbook. I use it for my reports and I do continuing development work to it. Sometimes I want to use it, sometimes I want to edit it.

I have the Auto_Open set up to check whether I opened this workbook. If the code sees me, then it asks whether I want to run or edit the code. If edit, the code isn't run. For all other users, the question is not asked and the code is automatically run.

This method is very simple and it works well for me.

 


Comment by: CareBear (9/22/2012 1:03:13 AM)

Thanks a bunch, you just save my script. Now I can keep my "application.visible = false" line and still be able to access the scripts when needed. Genius! Hope you have a great day!

 


Comment by: Hasan (11/10/2012 7:22:35 AM)

Thank you very much Guys!!! My ten days work.. Finally I am writing a Trial Version Code.. So that after trial period the word will not even open.. Now when I finish doing that I just lost my file.. And I couldn't able to open also.. So imagine my situation.. Thank you Thank you VERY MUCH!! You just saved my 10 days work. I am done otherwise!!
Best regards
Hasan

 


Comment by: Peter (12/24/2012 8:37:51 AM)

Thanks for that tip!
The difference between a bunch of re-writing and simply disabling a bit of code.

Cheers,
Peter

 


Comment by: Geir (2/14/2013 1:03:16 AM)

I use the vba command
Workbooks.Open(Filename:=FilAdr1, ReadOnly:=True, Password:="password").RunAutoMacros Which:=xlAutoOpen
to open a second workbook placed on a network server. This works fine with Excel 2003, but with Excel 2007 I am prompted for a password even though the password is provided in the code. Why is that?
Best regards
Geir

 


Comment by: Jan Karel Pieterse (2/14/2013 8:46:10 AM)

Hi Geir,

Odd. I just tried this and it works as advertised. There are no typo's in the password I assume?

 


Comment by: Geir (2/14/2013 10:52:53 AM)

Hi,

No, there are no typo's in the password, but I suspect there are some security settings in Excel or somewhere else that cause this to happen. Do you have any idea of what that could be? I could not figure that out.
I found similar problems posted on the web, but no answers provided.
Best regards
Geir

 


Comment by: Jan Karel Pieterse (2/14/2013 2:50:37 PM)

Hi Geir,

Perhaps you can email me a copy of the workbook with the password?

 


Comment by: MR (6/7/2013 8:51:00 PM)

Thank you very much for this tip! Kept me from having to re-create a rather complication file!!

 


Comment by: Robert (6/24/2013 8:26:14 AM)

Try this (from the Excel 2010 Help):


Dim secAutomation As MsoAutomationSecurity
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open Filename:="test.xls"
Application.AutomationSecurity = secAutomation


 


Comment by: Jan Karel Pieterse (6/24/2013 1:33:33 PM)

Hi Robert,

Thanks for the suggestion.
Note however that this method disables macros on the opened file, which makes debugging impossible.

 


Comment by: Chris (7/3/2013 2:43:21 PM)

Hi!

I keep having multiple files open during my excel 2010 startup. Earlier, I used macros to split tabs in one worksheet, to multiple worksheets, and I deleted them from the Visual Basic menu of excel once I completed.

However, they still keep loading automatically and I'm not sure where they're loading from. I think they're tied to the Personal.xls file in some way, though, no macro is listed for the module.

Please help. I need to delete all macros loading in start-up so that whenever I open an excel file, it only pulls up that file.

Thanks,
Chris

 


Comment by: Jan Karel Pieterse (7/4/2013 7:38:29 AM)

Hi Chris,

Check in File, Options, Advanced tab, section "General" whether a path is entered under "At startup, open all files in".

 


Comment by: Chris (7/4/2013 7:52:00 AM)

Thanks for the response Jan. I did that already, nothing listed under "At startup ..." Any other suggestions?

Thanks

 


Comment by: Jan Karel Pieterse (7/4/2013 5:28:41 PM)

Are those files by any chance stored in your XLSTART folder?

 


Comment by: Chris (7/4/2013 6:14:02 PM)

Hi Jan!

It's possible. How would I access the XLSTART folder? Maybe there's something I'm doing wrong.

Thanks

 


Comment by: Jan Karel Pieterse (7/4/2013 8:52:01 PM)

Just type this into the addressbat of Explorer:

%appdata%\microsoft\excel\xlstart

and press enter.

 


Comment by: Chris (7/4/2013 9:04:30 PM)

Thanks Jan!

I got the message, "Windows can't find %appdata%\microsoft\excel\xlstart. Check the spelling and try again. I'm using Windows 7, does this matter?

 


Comment by: Jan Karel Pieterse (7/5/2013 11:58:03 AM)

Hi Chris,

The % shortcut should lead you to here:

C:\Users\[yourusername]\AppData\Roaming\Microsoft\Excel\XLSTART

 


Comment by: Chris (7/10/2013 2:26:41 PM)

Thanks Jan! I had to do it on my work computer cause that's where I was having the issue. I deleted those file from the start-up folder and now it loads up just fine.

 


Comment by: Anthony Nashokigwa (12/18/2013 11:12:37 AM)

I have a windows task scheduler that opens my spreadsheet once a day. I have procedures embedded in Public Sub Workbook_Open of the spreadsheet which obviously run automatically when file is opened. The problem I'm having is that I would like Workbook_Open to be disabled when I personally open the file i.e. double clicking, to prevent the procedures from automatically running.

Any thoughts?

 


Comment by: Jan Karel Pieterse (12/18/2013 11:51:20 AM)

Hi Anthony,

Doesn't the suggested method on this page work?

 


Comment by: Anthony Nashokigwa (12/18/2013 12:42:10 PM)

Hi Jan - it does work, but I am trying to avoid as much manual intervention as possible, as multiple people will are using this file and at some point or another, someone is going to forget to hold down the SHIT key.Perhaps what I could do is write a macro that simulates holding down the the shift button whilst opening the file?

Your thoughts?

 


Comment by: Jan Karel Pieterse (12/18/2013 3:08:25 PM)

Hi Anthony,

I guess you should be asking yourself a question like "under which circumstances must the Workbook_Open event be ignored" and then act accordingly.

For example, if the scheduler is run from a machine with a specific user logged in, you could check for the environ("username") property value and decide whether or not to execute the content of Workbook_Open.

 


Comment by: Scott (4/7/2014 8:00:58 PM)

I have a macro that opens all the other workbooks in a folder that have macros to get some information about those files. I do not want the auto_open macros to run. However, they are running when the vb code opens them.

Supposedly, workbooks with auto_open macros will not automatically run if opened with vb code. I find this not to be true. They are running and I don't want them to. I wish to find a way to prevent this.

 


Comment by: Jan Karel Pieterse (4/9/2014 7:22:59 AM)

Hi Scott,

You could turn off macros of those files entirely:

Application.AutomationSecurity=msoAutomationSecurityForceDisable

 


Comment by: Arthur (6/24/2014 5:34:59 PM)

I have a file with macros, I have a specific macros that will delete certain information on the spread sheet if it's opened after a certain date, however I want to know if there's a way that if the user "disables macros" then the file will not open.

I get that a macro could do SO much stuff that that's why it's a security issue, but the security issue goes with wanting to make sure that if someone opens the file after a certain date, that certain info on the spread sheet gets deleted. *using the macro

or could i do the deleting with "conditional formatting"?

 


Comment by: Jan Karel Pieterse (6/25/2014 11:36:32 AM)

Hi Arthur,

The way this is often done is like so:

- Add a sheet which shows a message to the user about having to enable macros, add screenshots how to do it on that sheet too, as some people don't know that.

- In the BeforeSave event in the Thisworkbook module, make sure all sheets are hidden, except this macro warning sheet.

- At the end of the beforesave event, schedule a sub in a normal module that does the opposite (show all sheets, hide the macro warning sheet), so that when the user saves, he saves a file with just the macro sheet visible, but that when the save is done the normal state is restored.

- In the Workbook_Open event in ThisWorkbook, call that same scheduled macro that shows al sheets.

 


Comment by: David Evans (9/3/2014 10:53:33 AM)

Hi
I have one or two blank workbooks opening each time I open excel - I have exhausted all suggestions on many forums without luck, including the above - getting pretty desperate now as when I start to type formulas in a workbook the blank ones open preventing me from seeing the original page, they also grow in number and I end up getting a not enough resources to complete the task error even for simple tasks.
I have nothing in XLstartup, I have deleted xlb files but they reappear

Really hope you can help
Dave

 


Comment by: Jan Karel Pieterse (9/3/2014 11:53:27 AM)

Hi David,

Have a look at this page, perhaps it gives you some pointers:
http://www.jkp-ads.com/Articles/StartupProblems.asp

 


Comment by: Dave (9/30/2014 9:19:24 PM)

Hi Jan -

I have 4 macros in one workbook which are currently run manually on a monthly basis. Step 1 must be completed before Step 2 can be run before Step 3 can be run, etc.
I'd like to move to running these daily and with as little human intervention as possible. However, I can't nest Steps 2, 3 and 4 in Step 1 and just put them on "Auto_Open" b/c Step 1 calls on a non-Windows based program to update stock prices and other similar data. Depending on the amount of data, this can take several minutes, but it HAS to be finished before Step 2 can engage, or Step 2 will just be looking @ a bunch of cells with N/As, which will foul up Steps 3 and 4.

The best case scenario for me would be as follows:
1) Step 1 kicks off @ 00:00:00 (i.e. midnight)
2) I nest Steps 3 and 4 in Step 2 which kicks off 2 hours later (plenty of time for all data to update).

Is something like this doable?
Thanks.

 


Comment by: Jan Karel Pieterse (10/1/2014 10:23:27 AM)

Hi Dave,

I think your best bet would be to separate out the step that updates the external program into a new workbook so you can schedule that one prior to the other steps.

 


Comment by: Naveen kumar M (11/19/2014 12:37:57 PM)

thanks a lot...... ur suggestion helped me...

 


Comment by: Brian Hershman (12/21/2014 4:29:19 PM)

Jan,
I sometimes want to open Excel 2010 documents from (e.g.) Windows Explorer without the Workbook_Open macro running automatically.
When starting from Excel itself, the shift-open procedure you mentioned is fine.
But when starting from outside Excel (at least in Windows 7 Pro x64) , the same procedure does not work: Workbook_Open runs as normal.
Can you suggest how to do it in those circumstances?

 


Comment by: Jan Karel Pieterse (12/22/2014 11:08:58 AM)

Hi Brian,

I know, I find that annoying too. There isn't much you can do about it, other than:

- First opening Excel and following the instructions above
- Removing the file from the trusted files list (see my download page for a tool)
- Renaming the file first.

 


Comment by: Steve H (3/11/2015 7:32:07 AM)

I had the problem with the Auto_Open event still running when I opened the file from the windows file system, which as the developer I mostly do (rather than the 'file-open' method. I wanted to be mostly in 'developer' mode, but when I send out the file I wanted it to work in 'user' mode.

So - I simply include the following as the first line in the Auto_Open code:


    If Dir(ActiveWorkbook.Path + "\debug.txt") <> "" Then Exit Sub


In my working directory I then create an empty file called 'debug.txt' so for me the code never runs, but when I distribute the file it runs for everyone else.

 


Comment by: Jan Karel Pieterse (3/11/2015 9:38:17 AM)

Hi Steve,

Good idea. I have seen similar solutions, like a debug.txt file to enable debugging.

 


Comment by: Bill Benson (3/26/2015 6:21:27 PM)

I would like to know if there is a VBA driven approach to suppressing Auto_Open code in a workbook opened by a running macro in a current project. Often I am debugging code that another wrote and I do not want Excel settings messed with. What I am not sure about is whether turning EnableEvents to False will prevent code in a secondary workbook's Auto_Open procedure from running. I know it will suppress that workbook's Workbook_Open event code.

Thank you and good luck at the Summit.

 


Comment by: Jan Karel Pieterse (3/26/2015 6:25:32 PM)

Hi Bill,

Auto_Open routines never run when opening a workbook from VBA unless you use the runautomacros method, so you're safe.

Pity you couldn't make it to the Summit!

 


Comment by: Bill Benson (3/26/2015 7:28:22 PM)

Thank you for the quick response. Yes, the Summit would have been wonderful, but the timing - and the airline cost - was a bit prohibitive at this time. I am just starting a permanent job with Bob Umlas's firm, and he will be there. I hope he takes copious notes and comes back in a mood to share!!!! :)

 


Comment by: Tony (4/9/2015 10:46:03 AM)

I just wanted to say Thanks! I have been mashing my head against athe desk since yesterday morning because I didn't know that the left-shift trick works when opening Excel workbooks.

You have made me very happy. :)

 


Comment by: Rudi (6/24/2015 11:50:37 AM)

Thanks for the great tip.

 


Comment by: Hugo (10/23/2015 3:04:32 PM)

Excelent solution, thanks!

 


Comment by: Casandra (10/23/2015 4:33:59 PM)

Sometimes the answer is SO simple. This was extremely helpful!! Thanks!!

 


Comment by: Richard (11/21/2015 3:53:48 PM)

HI,
I have Windows 10 downloaded onto an original Windows 7 computer.
My Excel workbooks now open in compatibility mode.
I have VBA in Excel which is not compatible with the *.xlsx format. How can I open them in *.xls only so it shows the original 2003 Excel Format and nor the 'Ribbon' thing.
regards,
Richard
PS. This must affect a lot of other users.

 


Comment by: Jan Karel Pieterse (11/21/2015 10:54:44 PM)

Hi Richard,

The Ribbon is here to stay, no way back I'm afraid. I'd advise you to be patient, try it for a bit and you'll probably get used to it.

To save files with VBA, save them as xlsm file.

 


Comment by: Richard (11/22/2015 3:51:28 PM)

Hi Jan,
Thanks for your comment re: 11/21/2015 10:54:44pm
I wasn't very clear in my first question.
My Excel VBA is not compatible with the xlsm extension. So how do I run them in 97-2003. I can't run them in the compatibility mode.
Regards,
Richard

 


Comment by: Jan Karel Pieterse (11/23/2015 5:14:25 PM)

Hi Richard,

I'm afraid I fail to understand what the problem is.

What precisely happens when you try to run your macro?

 


Comment by: Richard (11/23/2015 8:21:10 PM)

Hi Jan,
The 'Run time error' comes up when I try to open the workbook. This is caused by the Customized toolbars failing to open with their specialized menus for the Workbook in question. I have eight different Workbooks all with their own Toolbars, including drop-down menus etc.
I can test no further without Toolbars.
Richard.

 


Comment by: Meme (4/13/2016 3:05:25 PM)

Hi Richard,

I don't know if your problem is solved, but this answere is also for others with the same problem.

VBA don't work on xlsx, only on xls. It doesn't matter witch Excel you have, you should save it as xls, otherwise the VBA or Macro failed to work, because xlsx don't support VBA.

 


Comment by: Jan Karel Pieterse (4/13/2016 4:04:32 PM)

Hi Meme,

Actually, the correct file type is either xlsm or xlsb. xls is the fileformat for Excel 2003 and older.

 


Comment by: Meme (4/13/2016 4:57:48 PM)

Hi Jan, you're right. That's the correct way to save it. But VBA will also open with saving it as xls, but not as xlsx.

 


Comment by: Ben (4/26/2016 12:12:02 PM)

Hi

I have been trawling the internet trying to find someone to help me and although my question is slightly of the topic of your post I am hoping as someone who clearly has a great deal of skill in VBA, perhaps you would help.

I am trying to write a piece of VBA code in excel that on open of the workbook, checks if the VBA password is still in place and not missing or been edited (by someone hacking it), and then if is missing etc, self delete the excel file.

Thank you for your time and help, I really appreciate it.

Best Regards

Ben

 


Comment by: Jan Karel Pieterse (4/26/2016 1:29:16 PM)

Hi Ben,

I'm afraid this isn't something you can easily do, at least the user must have allowed access to the VBA project.
Moreover, if the user has accessed your code, it is likely that the code that should remove the add-in is already removed by the user. Better to prevent access to the VBA code to start with, for example by using UnviewablePlus:
http://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html

 


Comment by: Himanshu (5/3/2016 6:17:12 PM)

Hello,

I have an excel workbook which is macro enabled.

I believe there is something wrong with its coding, due to this file hangs while opening. For the last two days it is not opening at all, I can only see a blank screen and rotating cursor. It is also returning a message that "Excel is not responding". This workbook is important for me. Please can you help me to open this file. if the issue can't be resolve, please suggest me a way so that I can at least copy my macro coding, it will help me to save my efforts.

Thanks
Himanshu

 


Comment by: Jan Karel Pieterse (5/4/2016 2:34:51 PM)

Hi Himanshu,

Have you seen this page perhaps: http://www.jkp-ads.com/Articles/CorruptFiles.asp

 


Comment by: Arjen (5/8/2016 6:52:47 AM)

Hello Jan,
my question is also slightly of the topic but i hope you can answer this.

I have made an auto_open file (xlsm) and set macro security to “Enable all macros”.
If I open this file in the normal explorer or via another VBA command “ShellExecute”, excel will open with an empty screen. The macro will not be executed…?
Also a shortcut on my desktop screen results in a nothing happens event???

The only way I can start the program including its macro event is that I have to open the MRU (most recently used files) and click once on the name…..
PC is windows 7 and Excel version is 2016

By the way, I have tested the same file on a windows 8.1 and Excel 2013 and it will work normally.
Hope you can help,

Thanks in advance, Arjen

 


Comment by: Jan Karel Pieterse (5/11/2016 3:57:23 PM)

Hi Arjen,

Have a look at this article, it may contain a solution for this problem: www.jkp-ads.com/articles/startupproblems.asp

 


Comment by: Leon (6/3/2016 12:04:01 PM)

one little note to add on this topic - holding shift while opening the file only works when opening in Excel.

I don't think there's a way to open an xlsm file without the macro running if you open it in My Computer, for example.

If I'm wrong, I'd welcome a solution..

thanks, Leon

 


Comment by: vAPL (3/18/2017 7:08:16 AM)

want to stop macro run automatically in open sheets except sheet for which macro is written

 


Comment by: Jan Karel Pieterse (3/20/2017 10:05:11 AM)

Hi vAPL,

I guess you could start each event with a test for which sheet is active and act accordingly:

If Activesheet.Name <> "TheSheetForThisMacro" Then Exit Sub

 


Comment by: Rick Walton (4/6/2017 10:54:21 AM)

If you alter the workbook.open event code to search the open workbooks for a specific named item (like editmode.xlsx), and if found then 'end', otherwise continue on with running the code. If you want to open without executing the code just make sure you have the killer workbook open prior to opening the macro enabled workbook.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].