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.
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.
- 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!
There was an opening error that blocked your algorithm. It’s OK now.
I apologize for my previous post, completely wrong.
Congratulations on your idea, that works very well
I’using Office365 with a customUI with tabs and Icons.
In This environment the code seems not to work as expected: it is always mbMacrosEnabled = false
Probably the sequence of events has changed and Ribbon.customUIonLoad is not the last?
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.
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?