Onkey to call Procedure on named sheet 1

[DrSimon]

I want to use OnKey to call a sub in a worksheet. I can get this to work:
Application.OnKey "%{F9}", "Sheet1.Called_Sub"
But I want to proof it against someone rearranging the sheets and refer to the sheet by name. Worse still the name of the sheet has spaces in it(and it's not mine to change). So I'm after something along the lines of:
Application.OnKey "%{F9}", "My Work Sheet.Called_Sub"

The error that comes up is: 'The macro [Full name including workbook name and path] cannot be found'. Does anyone know the syntax please?

Thanks
Simon Rouse

 

[SkipVought]

Simon,

Please post VBA questions in Forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DrSimon]

Er Skip, one of us is going mad. Isn't that where this is?

Simon

 

[combo]

In "Sheet1.Called_Sub" the "Sheet1" is a code name, it can be changed only in VBE. As long as the user has no access to VBE, change of sheet name in excel will not lock the code. The only risk is when the user deletes the sheet with code.
BTW, it is not permitted to have code name with spaces. Check what name you used and if a sheet with this code name exists.


combo

 

[SkipVought]

I'm so sorry. It must be ME that is delusional!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DrSimon]

Thanks combo I hadn't looked at it that way. Of course you're right and if someone deletes the sheet they won't want the code anyhow.

And Skip - it's good that not all the senior moments are mine! But with all the help you give, you're allowed to make the odd mistake.

Simon

 

[SkipVought]

Well do I have a better excuse than you, as I am pushing 67?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DrSimon]

Pushing 67 what? Have a good weekend