Disable Shift key on open

A user can prevent the Workbook_Open event from firing by holding down the Shift key during startup. Since Excel 2007 it is possible to ignore this, and run your code anyway.

When you hold down the Shift key during the startup of the attached workbook it will show this messagebox:

How is this possible? The trick is to utilize the onLoad event of the custom user interface of the ribbon.

<customUI onLoad="customUIonLoad"/>

You find the line above in file customUI.xml of the workbook. It defines a custom UI without any tabs, and tells Excel which procedure to call when the onLoad event is raised. This event is fired after the Workbook_Open event. Fortunately, Excel executes this event even when Application.EnableEvents is set to False, or when the user holds down the Shift key.

The callback is defined in the Ribbon module of the workbook:

Sub customUIonLoad(ribbon As IRibbonUI)
    ThisWorkbook.CheckShiftOnOpen
End Sub

The code in ThisWorkbook:

Private mbMacrosEnabled As Boolean

Private Sub Workbook_Open()
    mbMacrosEnabled = True
End Sub

Public Function CheckShiftOnOpen()
    If Not mbMacrosEnabled Then
        MsgBox "Don't press Shift on opening the workbook!", vbCritical, "Fatal error"
    End If
End Sub

If macro’s are enabled and the user didn’t hold the Shift key on startup, then in WorkBook_Open mbMacrosEnabled is set to true. The messagebox in CheckShiftOnOpen will not show.
If macro’s are enabled and the user does hold the Shift key, the Workbook_Open event is not raised and mbMacrosEnabled remains False. However, Excel loads the custom UI anyway and calls customUIonLoad: the messagebox will show.
If macro’s are disabled (because of security settings) then no macro will run, and nothing happens.

Final notes

• Above I wrote that “a user can prevent the Workbook_Open event from firing by holding down the Shift key”. In fact, holding down the shift key also prevents the Workbook_Activate and Workbook_WindowActivate events from firing. Note that saying “Holding Shift disables macro’s” is not true.
• Instead of showing a messagebox you could initialize your application as if the user didn’t hold the Shift key. You have to call all init functions in the Open event series “manually”, including trapped events if applicable. Personally I prefer to just close the workbook, after an announcement.
• If you want to show the user a message when macro’s are disabled because of security settings, you need the well known workaround of showing a startup sheet. You have to make sure that the workbook is always saved in this condition (use the BeforeSave and AfterSave events).

I hope this pixcel helps. Excel!

Download

IgnoreShiftOnOpen-v1.xlsm

See also

The sequence of events in workbooks