Solved: Excel 2007: Bypass Workbook_Open Code

[geekgirlau]

Have been grappling with Office 2007 differences, and came across a wonderful little gem!

I have a workbook that just takes a backup of a database and zips it up; requests to restore network backups can take days depending on the organisation, so this is my little local workaround.

Unfortunately on this particular workbook, holding down [Shift] does NOT bypass the Workbook_Open procedure, even when opening the workbook from within Excel.

The workbook is located within a "trusted location". One method would be to remove the trusted location and disable all macros, but this seems overkill to open and modify a single file.

My current workaround:

[VBA]
Sub EditBackupWorkbook()
Const cstrPath = "S:\MyPath\Backup_Database.xlsm"

Application.EnableEvents = False
Workbooks.Open cstrPath
Application.EnableEvents = True
End Sub

[/VBA]

If anyone has any suggestions as to why this might be occurring in the first place, I'm all ears

[Paul_Hossler]

This works for me in 2007

[vba]
Option Explicit
Private Sub Workbook_Open()
If IsShiftKeyDown Then Exit Sub
MsgBox "Hello"
End Sub
[/vba]


and Chip's Key procedure

[vba]
Option Explicit
Option Compare Text
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
' modKeyState
' By Chip Pearson, www.cpearson.com, chip@cpearson.com
' This module contains functions for testing the state of the SHIFT, ALT, and CTRL
' keys.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Declaration of GetKeyState API function. This tests the state of a specified key.
''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function GetKeyState Lib "user32" ( _
ByVal nVirtKey As Long) As Integer

'Find the window handle for this instance of Excel
Private Declare Function FindWindow32 Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
'Look in the message buffer for a message
Private Declare Function PeekMessage32 Lib "user32" Alias "PeekMessageA" (lpMsg As MSG32, _
ByVal hWnd As Long, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, _
ByVal wRemoveMsg As Long) As Long
'Translate the message from a virtual key code to a ASCII code
Private Declare Function TranslateMessage32 Lib "user32" Alias "TranslateMessage" (lpMsg As MSG32) As Long

''''''''''''''''''''''''''''''''''''''''''
' This constant is used in a bit-wise AND
' operation with the result of GetKeyState
' to determine if the specified key is down.
''''''''''''''''''''''''''''''''''''''''''
Private Const KEY_MASK As Integer = &HFF80 ' decimal -128
'''''''''''''''''''''''''''''''''''''''''
' KEY CONSTANTS. Values taken from VC++ 6.0 WinUser.h file.
'''''''''''''''''''''''''''''''''''''''''
Private Const VK_LSHIFT = &HA0
Private Const VK_RSHIFT = &HA1
Private Const VK_LCONTROL = &HA2
Private Const VK_RCONTROL = &HA3
Private Const VK_LMENU = &HA4
Private Const VK_RMENU = &HA5
'''''''''''''''''''''''''''''''''''''''''
' The following four constants simply
' provide other names, CTRL and ALT,
' for CONTROL and MENU. "CTRL" and
' "ALT" are more familiar than
' "CONTROL" and "MENU". These constants
' provide no additional functionality.
' They simply provide more familiar
' names.
'''''''''''''''''''''''''''''''''''''''''
Private Const VK_LALT = VK_LMENU
Private Const VK_RALT = VK_RMENU
Private Const VK_LCTRL = VK_LCONTROL
Private Const VK_RCTRL = VK_RCONTROL
'Type to hold the x and y coordinates of the mouse pointer
Private Type POINTAPI32
x As Long
Y As Long
End Type
'Type to hold the Windows message information
Private Type MSG32
hWnd As Long 'the window handle of the app
Message As Long 'the type of message (e.g. keydown, keyup etc)
wParam As Long 'stores the key code
lParam As Long '?
time As Long 'time when message posted
pt As POINTAPI32 'coordinate of mouse pointer when message posted
End Type
''''''''''''''''''''''''''''''''''''''''''''
' The following constants are used to specify,
' when testing CTRL, ALT, or SHIFT, whether
' the Left key, the Right key, either the
' Left OR Right key, or BOTH the Left AND
' Right keys are down.
'
' By default, the key-test procedures make
' no distinction between the Left and Right
' keys and will return TRUE if either the
' Left or Right (or both) key is down.
''''''''''''''''''''''''''''''''''''''''''''
Public Const BothLeftAndRightKeys = 0 ' Note: Bit-wise AND of LeftKey and RightKey
Public Const LeftKey = 1
Public Const RightKey = 2
Public Const LeftKeyOrRightKey = 3 ' Note: Bit-wise OR of LeftKey and RightKey

Public Function IsShiftKeyDown(Optional LeftOrRightKey As Long = LeftKeyOrRightKey)
''''''''''''''''''''''''''''''''''''''''''''''''
' IsShiftKeyDown
' Returns TRUE or FALSE indicating whether the
' SHIFT key is down.
'
' If LeftOrRightKey is omitted or LeftKeyOrRightKey,
' the function return TRUE if either the left or the
' right SHIFT key is down. If LeftKeyOrRightKey is
' LeftKey, then only the Left SHIFT key is tested.
' If LeftKeyOrRightKey is RightKey, only the Right
' SHIFT key is tested. If LeftOrRightKey is
' BothLeftAndRightKeys, the codes tests whether
' both the Left and Right keys are down. The default
' is to test for either Left or Right, making no
' distiction between Left and Right.
''''''''''''''''''''''''''''''''''''''''''''''''
Dim Res As Long

Select Case LeftOrRightKey
Case LeftKey
Res = GetKeyState(VK_LSHIFT) And KEY_MASK
Case RightKey
Res = GetKeyState(VK_RSHIFT) And KEY_MASK
Case BothLeftAndRightKeys
Res = (GetKeyState(VK_LSHIFT) And GetKeyState(VK_RSHIFT) And KEY_MASK)
Case Else
Res = GetKeyState(vbKeyShift) And KEY_MASK
End Select

IsShiftKeyDown = CBool(Res)
End Function
[/vba]

Paul

[geekgirlau]

How silly of me - why would one expect to still be able to hold a simple key, as opposed to calling a #@$% API function .

OK, I've had my little whinge. Thanks Paul, this worked beautifully, no matter how much I wish it weren't necessary!