Prevent Workbook_Open macro from running on opening

[KitaYama]

When I used to work with Excel (2 decades ago), I was able to press shift and click an Excel file to prevent running the code in Workbook_Open sub.

But it seems it doesn't work anymore.
I've tried shift+double clicking the icon
And
Launching Excel, going to File in ribbon and Open tab, then shift+clicking the file name in the list.

In any case the code is executed.
I have to change trust settings anytime I need to edit the sub.

How can I open an Excel book and prevent the code in Workbook_Open sub being executed?

Office 365
Windows 11

Thanks.

 

[gordsky]

sounds daft but have you tried holding the shift continuously until the file has finished loading rather than when you just click open?

 

[jkpieterse]

Nope, that feature has been removed from Excel, unfortunately. A workaround is to include a test for the presence of a small txt file in the same folder as the Excel file. Something like this (untested):

Private Sub Workbook_Open()
    If Len(Dir(ThisWorkbook.Path & Application.Pathseparator & "NoWorkbookOpen.txt"))> 0 Then Exit Sub
'Rest of code

 

[KitaYama]

sounds daft but have you tried holding the shift continuously until the file has finished loading rather than when you just click open?

Sure. But nothing happens.

 

[KitaYama]

Nope, that feature has been removed from Excel, unfortunately. A workaround is to include a test for the presence of a small txt file in the same folder as the Excel file. Something like this (untested):

Private Sub Workbook_Open()
    If Len(Dir(ThisWorkbook.Path & Application.Pathseparator & "NoWorkbookOpen.txt"))> 0 Then Exit Sub
'Rest of code

Thanks. I think I can do that.

 

[Peter_SSs]

A workaround is to include a test for the presence of a small txt file in the same folder as the Excel file.

Hi Jan Karel. So if you received a workbook from somebody else and were concerned about possible Workbook_Open code would the only option be to ensure that you had
- macros disabled, and
- the workbook not in a trusted location?

 

[jkpieterse]

- macros disabled, and
- the workbook not in a trusted location?

Hi Peter,

Yes that is the only safe approach. In fact, your first option sums it up. I often rename a workbook just so it isn't trusted anymore. Or I use my tool to remove it from trusted documents: Dozens of Free Excel Downloads For You

 

[Peter_SSs]

Cheers. Thanks for the confirmation.