Onkey method

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fishingsantafe - 13 Jan 2006 03:39 GMT

How do you assign a short cut key to a macro I created. I am tryin to learn
VBA and am working on writing code. I have been reading about the onkey
method. Where in the code to you enter the short cut key? After the Dim
Statement?

Could some one give me a couple of easy examples or tell where I can find
the code or symbols used for short cut keys? What happens if I use a key
that is already assigned to do something else? Can it recognize when to use
it to run a macro and when to use it for it original purpose?

I am just learning VBA and trying to learn to code macros. I have manged to
code and edit the macro to get it to do what I wanted but can not seem to
figure out how to attach a short cut key in the code.

Also does anyone know if there is a way to create macros in Excel and use
them in another application that uses VB? How do you tie the two together?

Any help is appreciated.

Reply to this Message

Dave Peterson - 13 Jan 2006 14:35 GMT

You can assign a shortcut key via:

Tools|macro|macros...
select your macro from the list
then click options

I'd suggest a combination of ctrl-shift-(letter) to stay away from built in
shortcuts.

Alternatively, you can dump the shortcut keys and use menus/toolbars to invoke
your macros.

If you want to add an option to the worksheet menu bar, I really like the way
John Walkenbach does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

======
And your second question has a few answers. If the other application can be
automated (like MSWord/Powerpoint/outlook/others that support VBA(????), you can
do it fine. (Well, if you know that other application well enough to write a
useful macro.)

If the other application doesn't support automation, then maybe you could use
Sendkeys to control it (maybe unreliable).

If you have a more specific question, I'm sure you'll get a better answer.

> How do you assign a short cut key to a macro I created. I am tryin to learn
> VBA and am working on writing code. I have been reading about the onkey
[quoted text clipped - 14 lines]
>
> Any help is appreciated.

Signature

Dave Peterson

Reply to this Message

David McRitchie - 13 Jan 2006 15:47 GMT

And in answer to still another part of your question:
If you assign a shortcut you will override the default usage.
To help you recognize existing shortcuts see
http://www.mvps.org/dmcritchie/excel/shortx2k.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> > Could some one give me a couple of easy examples or tell where I can find
> > the code or symbols used for short cut keys? What happens if I use a key
> > that is already assigned to do something else? Can it recognize when to use
> > it to run a macro and when to use it for it original purpose?

Reply to this Message

Fishingsantafe - 13 Jan 2006 21:51 GMT

If I were to use the short cut keys lets say ctrl-shift-c how would write the
code for this and would this fall right under the dim statement? I will look
into the toolbar thing and if i have any question I will write

>You can assign a shortcut key via:
>
[quoted text clipped - 32 lines]
>>
>> Any help is appreciated.

Reply to this Message

Dave Peterson - 13 Jan 2006 22:39 GMT

I'd assign the shortcut key manually, but you could use something like:

Option Explicit
Sub testme01()
MsgBox "hi"
End Sub
Sub Enable()
Application.OnKey Key:="^+c", procedure:="testme01"
End Sub
Sub Disable()
Application.OnKey Key:="^+c", procedure:=""
End Sub

You could look at macrooptions in VBA's help, too.

> If I were to use the short cut keys lets say ctrl-shift-c how would write the
> code for this and would this fall right under the dim statement? I will look
[quoted text clipped - 40 lines]
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200601/1

Signature

Dave Peterson

Reply to this Message

Fishingsantafe - 13 Jan 2006 21:59 GMT

Thanks for responding. I know this can be done with the application (I heard
of someone else that did it) I just can not figure out how the two tie
together. This application uses OLE Automation like excel does. I also
coded a macro in this application a using VBA languabe and it worked just
fine so I know it understands VBA. But if I record a macro in this
application I can see It uses send keys when I go to edit.

I just want to be able to record in excel some repetitive tasks that I do in
this other application. I want to be able to use these macros that I
recorded in excel in this other application.

>You can assign a shortcut key via:
>
[quoted text clipped - 32 lines]
>>
>> Any help is appreciated.

Reply to this Message

Dave Peterson - 13 Jan 2006 22:40 GMT

If you post the name of the other application, you may get some specific help.

I don't have a guess.

> Thanks for responding. I know this can be done with the application (I heard
> of someone else that did it) I just can not figure out how the two tie
[quoted text clipped - 47 lines]
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200601/1

Signature

Dave Peterson

Reply to this Message