Running macro on any key pressed event.

[Premanshu]

Hi,

Can anyone please advice me how to make a code so that it captures the event of pressing any key of the keyboard and triggers a macro.

I tried using OnKey method but the problem with this is I am getting stick to any particular button whichever i specify in the onkey method wheareas I want to keep it general for any button in the keyboard.

Also if anyone can suggest how can I use OnKey method to use the "S" key to launch a macro.


Please help

Regards,
Premanshu.

 

[crimson_b1ade]

THE BELOW CODE GOES IN THE WORKBOOK MODULE

Private Sub Workbook_Open()
 
Application.OnKey "s", "myMsgToYou"
 
End Sub

THE BELOW CODE GOES IN A NEW MODULE

Private Sub myMsgToYou()
 
MsgBox "You pressed the letter s!"
 
End Sub

 

[RoryA]

Can I ask why? That sounds a little like keylogging software to me...

 

[Premanshu]

thank you Crimson for your responce >
it works for me.


Rorya : No it's not a keylogging software (I am working on excel VBA). I am working on an excel model which has several macros in it for performing different tasks. All these tasks are listed on the Main page of this excel model, now the user has to select what he wants to perform on the data which he'll have to locate once the model opens. The list of tasks one can perform is large (197 tasks) which does not fits into the screen and user have to scrol down to see all of them. So I have thought of a feauter which would enable the user to trigger the desired macro on hitting a single button on his keyboard instead of scrolling through the list and clicking on the button beside the task. Some of the tasks/macros whould be enabled to trigger on a event of pressing some combination of keys also.

Please advice if I can code the model in this way. If this is possible.

Regards,
Premanshu.

 

[RoryA]

You can use OnKey as suggested but:
1. It will make it difficult for your users to type anything!
2. There aren't 197 keys!
Have you tried using a menu system instead?

 

[Premanshu]

Hi rorya,

I am not going to assign keys to every task, i'll do that for some selected tasks which are used very often (23 tasks). By the way can you please explain me Menu system.... may be if I can use it and if it prooves to be the best solution to me....


Regards,
Premanshu.

 

[RoryA]

Which version of Excel are you using?

 

[Premanshu]

I am using Excel 2003 (sorry for this I should have given this information earlier only)

 

[RoryA]

You basically create your own toolbars like the Standard and Formatting ones for example. If you can group the macros then you can have dropdown menus (like the File and Edit menus on the main menu bar) with various controls within that. Would that work?

 

[Premanshu]

hmm I am actually lost..... not this much versed with vba.... not sure how you are advicing me to do it...