VBA catch the "calculate sheet (shift+f9)" and "calculate workbook" events

Question

I don't know if this is trivial or actually hacky to do : is it possible to catch the "calculate sheet (shift+f9)" and "calculate workbook" events in VBA ?

I want to hide some processes that manipulate a few thousands lines, to just display some key values. I am calculating a distribution, the thousands lines, and want to just output the percentiles and some stats, as well as the graph instead of all these lines. At the moment I was thinking of a classic macro button, but my users are keener on the F9 way of life..

Do you think I'm pointing towards something interesting with what I suggest in the title ?

Answer 1

The OnKey command allows you to disable any key or key combination or make that key do something different.

This macro will be executed automatically when the workbook containing it is opened.

Sub Auto_Open()

  Application.OnKey "{F9}", "HandleF9"
  Application.OnKey "^{F9}", "HandleCtrlF9"
  Application.OnKey "+{F9}", "HandleShiftF9"

End Sub

The following routines will be executed instead of Calculate.

Sub HandleF9()
  Debug.Print "F9 clicked"
End Sub
Sub HandleCtrlF9()
  Debug.Print "Ctrl+F9 clicked"
End Sub
Sub HandleShiftF9()
  Debug.Print "Shift+F9 clicked"
End Sub

See OnKey help for more information.