Preventing Workbook_Open events from running (2)

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?

Jan Karel Pieterse

Posted in Uncategorized

6 thoughts on “Preventing Workbook_Open events from running (2)

  1. 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.

  2. The shift key does not work for me to prevent the Workbook_open event in Excel 2007.

  3. 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.

  4. Would something like this work for you?

    Declare Function GetAsyncKeyState Lib “user32” (ByVal vKey As Long) As Long

    Const VK_SHIFT = &H10

    If GetAsyncKeyState(VK_SHIFT) <> 0 Then Exit Sub
  5. @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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.