How to avoid the hold shift command to unable startup macros?

**-Citizen** · 02-21-2022, 04:18 AM

Hi,

So, I have an excel file that opens if a certain filepath on the computer is valid. If it is not valid the excel file will automatically close.

This code is dependent on the startup macro of the file.

I just realized that if someone opens the file while holding down shift, they unable this command completely and may use the file however they want.

Is there any way to avoid this? I mean, I thought about starting a timer as well that search for the file every 5 minutes, but that timer is also dependent on the startup macro.

Any way I can make my sheet more bulletproof and maybe avoid this silly shift-command trick?

**TMS** · 02-21-2022, 04:47 AM

See: https://bettersolutions.com/vba/events/auto-open.htm

Basically, if the user holds down the shift key when the workbook is opened, neither approach fires. However, you could run the Auto_Open macro manually.

If you define a global variable, say, bWBOrun, you can set it to True when either of those macros is run. If you test it, and it is False, you can manually run the Auto_Open macro. Maybe test it as the first thing you do in the Calculate Event handler and/or the Selection Change Event handler.

Note that, if someone does not enable macros, none of your macros work and functionality is lost.

**:) Sixthsense :)** · 02-21-2022, 04:48 AM

Try this method:-

https://www.teachexcel.com/excel-tut...xcel_1390.html

**-Citizen** · 02-21-2022, 08:34 AM

Hi!

Thanks for the reply!

TMS,

Just have to ask to be sure what you mean, because when I open the excel file, a message pops up and it starts if the conditions are fullfilled. If not fullfilled than it closes. As you have understood, when holding down shift it basically jumps over the "protection" and the sheet is now wide open and works. However, by using the method you describe here, there will be a macro that is actually testing if the opening macro has been run or not, and if not, it will run the opening macro.

So, needless to say, running the auto open function every time a formula is being used will be extremely annoing for the user, but you are suggesting that it runs a "check" before it runs the "auto open" function right? So, it checks if it has been run before it runs again?
Could you show me an example on how this code will be?

I am sorry, I will probably use so many hours if I am trying to figure it out myself

**-Citizen** · 02-21-2022, 08:37 AM

Originally Posted by :) Sixthsense :)

Try this method:-

https://www.teachexcel.com/excel-tut...xcel_1390.html

Thanks, I have actually seen this, my only problem is that it may be able to autosave or save (without closing the file) and then copy the file and opening it again using the shift-command and by-passing the security created.

**TMS** · 02-21-2022, 10:04 AM

This example file demonstrates the principle. Please try the attached sample workbook. To be fair, I can't get either of the Open routines NOT to run by pressing the Shift key. Maybe it's my laptop. Anyway, give it a try.

In the ThisWorkbook module:

Please Login or Register  to view this content.

In a Standard module:

Please Login or Register  to view this content.

**PrizeGotti** · 02-21-2022, 10:13 AM

Just out of curiosity, why would anyone hold down shift while opening an excel file?

**TMS** · 02-21-2022, 10:20 AM

Because they can. And it is supposed to stop the execution of the On Open macro(s) ... just nor for me, at the moment.

**PrizeGotti** · 02-21-2022, 10:24 AM

Isn't that about to be the default setting in the next Office update? How would this effect in the long run when holding shift is no longer required?

**Kyle123** · 02-21-2022, 11:43 AM

Yes, I think so, macros will be disabled by default for downloaded/emailed files with no option to enable them. I think the easiest solution if they need to be distributed is to either sign the code or encourage users to put them in trusted locations.

**-Citizen** · 02-22-2022, 03:56 AM

Originally Posted by TMS

Because they can. And it is supposed to stop the execution of the On Open macro(s) ... just nor for me, at the moment.

Thank you for helping out TMS!

So, try opening excel (without opening a file), then you chose open, find your file and then try holding shift and double click the file. Then the auto open macros will not run.

PrizeGotti: The default settings by not enabling macros are basicallly not the same considering that if macros are not enabled, the sheet will not work properly.
However, this is interesting considering that this may be a problem for all auto open macros and by enabling macros after the sheet is open may not start the auto open macros created.

**TMS** · 02-22-2022, 06:00 AM

You're welcome. Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

How to avoid the hold shift command to unable startup macros?

LinkBack

Thread Tools

Rate This Thread

Display

How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Re: How to avoid the hold shift command to unable startup macros?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Unable to run startup Macro in Excel 2007

Macro - Hold view and avoid confirm delete message

No Macros; startup message

[SOLVED] How to avoid "update links" startup prompt?

Command Line Startup Switches

[SOLVED] SHIFT doesn't prevent macro from running at startup

[SOLVED] Unable to load files from alternate startup folder

Command.bar@startup

Bookmarks

Bookmarks

Posting Permissions