Catching keyboard events

[kadeo]

I'm looking for a way to catch all keyboard events. I need to test the state of a Ribbon button whenever a key is pressed so that I can run other code based on the state of that Ribbon button. The key or combination that is pressed is not important, just the state of the Ribbon button when any key has been pressed.

I have a Custom Ribbon that has a button which uses the PasteValues IDMSO and so it's enabled state is controlled by Excel. I have another button that should only ever be enabled when this PasteValues button is enabled. I have code that controls the button sufficiently when Paste Values becomes enabled or disabled through Excel's UI but this does not work when it is enabled or disabled through keyboard shortcuts.

Whenever a key is pressed, I want to test the state of the Paste Values button and enable or disable my other button accordingly. It is not important that key strokes be monitored when Excel is in 'cell edit' mode or a userform is running etc.

I could go a different route here and use application.onkeys to remap what specific key combinations like "shift+insert" or "ctrl+v" do but there's always the chance that something gets missed. For instance, when the Paste Values button is enabled, just editing a cell is enough to disable it as editing the cell changes the CutCopyMode property on entry. Since any key on the keyboard (effectively) can put Excel into 'cell edit' mode I think I really need to be monitoring the state of the Ribbon Button when any key was pressed.

[LJMetzger]

Hi kadeo,

The following may help you get started. The code was tested and working using 32 bit Excel, but has never been compiled using 64 bit Excel.

The file contains the following code:

Please Login or Register  to view this content.

Here is additional code which may help you trap special keys. This code has never been tested in conjunction with the code in the file:

Please Login or Register  to view this content.

Lewis

[kadeo]

Thanks for the reply Lewis

Yeah, I'd seen that a few times when I was Googling. It seems real convoluted for what I need though. I know in a userform i can use the likes of keydown and keyup. It just seems real strange to me that something so basic (seemingly to me anyway) isn't covered for in normal sheets.

Still, I live in hope that somebody has a simple workaround.

[LJMetzger]

I know in a userform i can use the likes of keydown and keyup

I was hoping for that too. I haven't found the equivalent of Userform KeyDown and KeyPress events. After extensive searching a while ago, the code I posted was the best I could find.

[kadeo]

Is there anybody else can help now there's been a week to mull it over?

[Kyle123]

There isn't a simple way, what Lewis has given you is the only way

[kadeo]

Unreal! Thanks Kyle. It sucks but I guess that's this thread solved. Thanks for your help too Lewis!