Running macros from shortcut keys

When you record a macro, you can assign it a shortcut key. Is there a way
to assign a shortcut key to a macro that already exists?

Also, is there a way to intercept function keys (like, say, F9) and run a
macro along with the normal function of the key?
I have a number of non-volatile UDF's I'd like to update when I choose,
and linking them top F9 seems a fitting thing. If there's a better way,
I'll happily accept it.

-----------
Darren

On Sat, 18 Jun 2005 17:53:42 +0100, Darren Hill
<[email protected]> wrote:

> When you record a macro, you can assign it a shortcut key. Is there a
> way to assign a shortcut key to a macro that already exists?

I've figured out the answer to the question above, but not the one below.



> Also, is there a way to intercept function keys (like, say, F9) and run
> a macro along with the normal function of the key?
> I have a number of non-volatile UDF's I'd like to update when I choose,
> and linking them top F9 seems a fitting thing. If there's a better way,
> I'll happily accept it.
>
> -----------
> Darren

Have a look at Application.Volatile in help.



"Darren Hill" <[email protected]> wrote in message
news:op.sskr3ic0ed89cl@omega...
> On Sat, 18 Jun 2005 17:53:42 +0100, Darren Hill
> <[email protected]> wrote:
>
>> When you record a macro, you can assign it a shortcut key. Is there a
>> way to assign a shortcut key to a macro that already exists?
>
> I've figured out the answer to the question above, but not the one below.
>
>
>
>> Also, is there a way to intercept function keys (like, say, F9) and run
>> a macro along with the normal function of the key?
>> I have a number of non-volatile UDF's I'd like to update when I choose,
>> and linking them top F9 seems a fitting thing. If there's a better way,
>> I'll happily accept it.
>>
>> -----------
>> Darren

"Darren Hill" <[email protected]> wrote in message
news:op.sskrvsjyed89cl@omega...
> When you record a macro, you can assign it a shortcut key. Is there a way
> to assign a shortcut key to a macro that already exists?

Tools>Macros>Macros... select your macro and click Options

> Also, is there a way to intercept function keys (like, say, F9) and run a
> macro along with the normal function of the key?
> I have a number of non-volatile UDF's I'd like to update when I choose,
> and linking them top F9 seems a fitting thing. If there's a better way,
> I'll happily accept it.

Why not just precede the code by

Application.Volatile

On Sat, 18 Jun 2005 18:10:47 +0100, Bob Phillips
<[email protected]> wrote:

> Why not just precede the code by
>
> Application.Volatile

I don't want the values to update whenever the sheet is calculated, only
when I choose to. But I figured a way to do it - put a cell reference as
optional input for each of the UDF's, then have a button which sticks a
random number in that cell whenever it's clicked.
Out of curiousity, is there a way to capture key inputs like F9?

------------------
Darren

application.onkey "{F9}","xyz"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Darren Hill" <[email protected]> wrote in message
news:op.ssks5ev4ed89cl@omega...
> On Sat, 18 Jun 2005 18:10:47 +0100, Bob Phillips
> <[email protected]> wrote:
>
> > Why not just precede the code by
> >
> > Application.Volatile
>
> I don't want the values to update whenever the sheet is calculated, only
> when I choose to. But I figured a way to do it - put a cell reference as
> optional input for each of the UDF's, then have a button which sticks a
> random number in that cell whenever it's clicked.
> Out of curiousity, is there a way to capture key inputs like F9?
>
> ------------------
> Darren

On Sat, 18 Jun 2005 19:35:35 +0100, Bob Phillips
<[email protected]> wrote:

> application.onkey "{F9}","xyz"
>
Am I currect in assuming this would have to be in some kind of macro that
was running constantly in the background? How would you do that? A pointer
to a webpage where I could research this myself would be fine.
Could it be in a worksheet event (I don't see a BeforeKeyRelease sadly)?

Darren

Set it on workbook open, and reset on close

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{F9}"
End Sub

Private Sub Workbook_Open()
Application.OnKey "{F9}", "xyz"
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Darren Hill" <[email protected]> wrote in message
news:op.ssk1vhobed89cl@omega...
> On Sat, 18 Jun 2005 19:35:35 +0100, Bob Phillips
> <[email protected]> wrote:
>
> > application.onkey "{F9}","xyz"
> >
> Am I currect in assuming this would have to be in some kind of macro that
> was running constantly in the background? How would you do that? A pointer
> to a webpage where I could research this myself would be fine.
> Could it be in a worksheet event (I don't see a BeforeKeyRelease sadly)?
>
> Darren

That's fantastic - it works perfectly. Thanks very much, Bob.


On Sat, 18 Jun 2005 23:28:28 +0100, Bob Phillips
<[email protected]> wrote:

> Set it on workbook open, and reset on close
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Application.OnKey "{F9}"
> End Sub
>
> Private Sub Workbook_Open()
> Application.OnKey "{F9}", "xyz"
> End Sub
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
>
------------------
Darren