Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA to list key bindings (OnKey ?)

I am working with a large set of Excel vba scripts and some of them are bound to Ctrl-key combinations.

I know how to set one at a time in the user interface for Excel: Pull up the list of vba scripts (Alt-F8), select a script, and click Options. Then you can bind/unbind the script to a Ctrl-key combo. However, you can bind more than one script to the same key, and Excel will pick one (probably the first one it finds in some manner) and ignore the other bindings.

So, I want to assign Ctrl-e to a script, but first I have to find what other script(s) it is currently bound to, out of a list of hundreds of scripts.

Is there a way to get Excel to list the current key bindings (with a VBA macro, I suppose)? I have seen a solution for Word, that examines the "KeyBindings" collection. This is not working in Excel, though. Does Excel have a different object?

like image 484
Mark Goldfain Avatar asked May 31 '13 16:05

Mark Goldfain


People also ask

How do you check if a keyboard shortcut exists for a macro?

You can easily look up your keyboard shortcuts by by searching the VBA code for the word “onkey.” Use the Find window ( Ctrl + F ) in the VB Editor. If multiple macros or workbooks use the same shortcut, you can control the order or priority of the macros that run.

What is the use of OnKey?

This form of OnKey changes the normal result of keystrokes in Microsoft Excel. If Procedure is omitted, Key reverts to its normal result in Microsoft Excel, and any special key assignments made with previous OnKey methods are cleared.

What is OnKey?

OnKey. It is possible to run your macros when a particular key or key combination is pressed. This method can only be used to assign macros to shortcut keys.


1 Answers

After searching for a while, I could not find any possibility to programmatically get a list of all key bindings.

However, if you basically want to find out, which procedure runs on a shortcut, but you're not sure and don't want to crawl through your Personal Workbook, Add-ins, etc., you can create a dynamic breakpoint that will always stop on the first line of VBA code executed. To do this, simple use the Add Watch dialog (right click somewhere in the code window) enter the following parameters:

Expression: Timer, Procedure: All Module: All, Watch Type: Break when value changes

Then, simply execute the shortcut you're interested in - and the VBE will show you the routine that is bound to it...

like image 132
Peter Albert Avatar answered Sep 23 '22 04:09

Peter Albert