In Preventing auto_open and Workbook_Open events from running I descibed how to do exactly that. Well, it appears that the methods I gave do not always work with Excel 2007.
A visitor of my website complained he could not prevent his Auto_Open macro from running and during a BeamYourScreen session I took over his desktop and confirmed he was right (of course he was).
The situation where we couldn’t prevent an Excel automacro from running was like this:
– You have set up a trusted folder
– The folder is on a network share
– You’re using the UNC path to that folder.
Since I don’t have a network, I can’t easily test this.
Can anyone confirm whether the shift key trick works in these cases:
– Trusted folder on network (but with an assigned drive letter)
Same as above, but for Office 2010?
Thanks!
Jan Karel Pieterse
www.jkp-ads.com
Hi Jan Karel,
When I need to do this over a remote connection I just open a blank workbook in Excel and type in this three-line macro:
Sub OpenBook()
Application.EnableEvents = False
Workbooks.Open Application.GetOpenFilename()
Application.EnableEvents = True
End Sub
Should work in all cases.
Rob: Of course that works. But I’m stubborn, I want my shift key to work too :-)
The shift key does not work for me to prevent the Workbook_open event in Excel 2007.
If I’m developing and I don’t want the regular interface for the workbook to be built (i.e., toolbars, hissen and prtected sheets, etc.), I usually put an empty text file called, I dunno, debug.ini, in the same directory as the workbook. The Workbook_Open code checks for this file, and if it exists, it bypasses what I don’t want to run. Then I can change the file name to _debug.ini when I want to see what the user sees.
I think I got this trick from PED, so thanks, Rob.
Would something like this work for you?
…
Const VK_SHIFT = &H10
…
If GetAsyncKeyState(VK_SHIFT) <> 0 Then Exit Sub
@Jon: Yes I read that one too.
@Rob: I use that API whenever I need Workbooks.Open in my code to ensure the shift key is NOT pressed :-)
I still think the shift key trick whould work out of the box for 2007, not by some workaround.