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:

IgnoreShiftOnOpen

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

Comments

  1. Roger says:

    Correct me if I am wrong: If someone opens this workbook in the UI editor and deletes the code, then the protection of the Shift key is lost. If this is true, how do you protect against that?

    • Yes Roger, that is true. You could set a password on your vba project but that is not difficult to hack, if you need to. Other approaches: move all your code to a dll; set a password on your excel file (impossible to hack) and use an exe frontloader which knows the password.
      Too bad we can’t save Excel files to a binary, like Access.

      • Bart says:

        Hello Frans. What do you mean by “Too bad we can’t save Excel files to a binary, like Access.”. The .xlsb file format is binary, right? And so far, I haven’t found any articles or blogs explaining how to hack into password protected VBA code sitting inside a .xlsb file.

      • Would saving the xlsb to xlsm help?

Speak Your Mind

*