PDA

View Full Version : Excel VBA "OnKey" command problem



TomSmith
03-02-2007, 11:30 AM
(hello, first time poster here, apologies if this doesn't come out right)

I want to use the 'OnKey' VBA command so that some action is taken when a particular key is pressed in Excel.

I tried the Excel VBA Help examples, Walkenbach's examples from Power Programming, and a few examples from internet resources. All seemed to use the same basic syntax but it doesn't work for me. Yes, I can use the OnKey command to turn off or restore action. The problem is with directing the action to be taken to a new routine.


For example,
Sub tom1()
Application.OnKey "{F1}", "tom2"
End Sub
'
Sub tom2()
'some action to be taken
msgbox "hello whirled"
End Sub

Gives error message: "The macro "c:\myfile.xls'!tom1' cannot be found."

What I finally found is that the macro (in this case, "tom2") was found if all of the code was in the Personal.xls file. Can this be gotten to work if it's in a different file? I have several other files that serve as my "personal.xls" file and I don't really want to be forced to use this file as well.

Bob Phillips
03-02-2007, 11:40 AM
You have to have it in an open workbook, so surely Personal.xls is the best place for it?

TomSmith
03-02-2007, 12:10 PM
I have 3 other very large workbooks that auto-open with Excel. They contain numerous macros to add extensive menus and commands. I could use the Personal.xls for this one particular command and be done with it. But it would be nice to know how to force the action in other workbooks, or to know for certain that it's not possible.

(I should really tidy up those other workbooks. The handy tools accumulated over 15 years could probably stand some housecleaning.)

Thank you for your help.