• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with binding a macro to a shortcut key combnation

fable

New Member
Hi,
I'm relatively new to Excel and am doing a Spreadsheet assignment as part of an engineering course I'm doing.
I created a macro with the following coding:
Private Sub CommandButton1_Click()
Range("K1").Value = "Spreadsheet assignment checked and completed"
End Sub

This works fine.
My problem is I can't bind it to a shortcut key (which is a criteria for the Spreadsheets unit).
I've put the following coding into a separate module of the macro:
Sub CreateShortcut()
Application.OnKey "+^(c)", "CommandButton1_Click"
End Sub

Whenever I press +^(c) nothing happens. I've tried multiple variations, and have looked up other methods online but I cannot work out why its not working.
Does anyone have any suggestions of what I'm doing wrong in terms of the coding any place? Have I misunderstood what I'm doing?
Thanks in advance.

fable.
 

p45cal

Well-Known Member
Application.OnKey "^C", "CommandButton1_Click"
or
Application.OnKey "^+c", "CommandButton1_Click"

but I'd guess that CommandButton1_Click is in a sheet's code-module so you might have to use something like:
Application.OnKey "^C", "Sheet2.CommandButton1_Click"

There's another way, but only if the Sheet2.CommandButton1_Click macro is not Private:
Application.MacroOptions Macro:="Sheet2.CommandButton1_Click", HasShortcutKey:=True, ShortcutKey:="C"
(This last will need Shift+Ctrl+c to be pressed for it to run the macro.)
 

fable

New Member
Application.OnKey "^C", "CommandButton1_Click"
or
Application.OnKey "^+c", "CommandButton1_Click"

but I'd guess that CommandButton1_Click is in a sheet's code-module so you might have to use something like:
Application.OnKey "^C", "Sheet2.CommandButton1_Click"

There's another way, but only if the Sheet2.CommandButton1_Click macro is not Private:
Application.MacroOptions Macro:="Sheet2.CommandButton1_Click", HasShortcutKey:=True, ShortcutKey:="C"
(This last will need Shift+Ctrl+c to be pressed for it to run the macro.)
Hi,
Thank you for your reply, much appreciated.
I put in the codes you suggested and every time I press Ctrl+Shift+c the following message appears.

59528

I've gone into Macro security and enabled all macros (I also made the sub non-private) and this seems to have no effect.
I have already saved the document as a Macro-enabled excel workbook so I don't know why it would say that the macro isn't available on the workbook.
Any suggestions on what I need to do next?

Thanks again!
fable
 

p45cal

Well-Known Member
Is the macro you're calling in Sheet2's code module? or another sheet or elsewhere?
There is a macro called CommandButton1_Click isn't there?
Did you use Onkey or MacroOptions?
Attach the file here.
You can also create the shortcut manually.. does it have to be done with code?
OnKey will be forgotten when Excel is closed so the workbook will need the code that creates the shortcut to be run on opening it or on activating the relevant sheet.
 
Last edited:

fable

New Member
Is the macro you're calling in Sheet2's code module? or another sheet or elsewhere?
There is a macro called CommandButton1_Click isn't there?
Did you use Onkey or MacroOptions?
Attach the file here.
You can also create the shortcut manually.. does it have to be done with code?
OnKey will be forgotten when Excel is closed so the workbook will need the code that creates the shortcut to be run on opening it or on activating the relevant sheet.
 

Attachments

p45cal

Well-Known Member
Is the macro you're calling in Sheet2's code module? or another sheet or elsewhere?
No it's not, it's in Sheet1's code sheet module.
I specifically emphasised 'like' in my response earlier:
but I'd guess that CommandButton1_Click is in a sheet's code-module so you might have to use something like:
Application.OnKey "^C", "Sheet2.CommandButton1_Click"
which means the command should be:
Application.OnKey "^C", "Sheet1.CommandButton1_Click"

You didn't answer this:
You can also create the shortcut manually.. does it have to be done with code?
Using OnKey has its problems:
If you close Excel down altogether, then open MSRA2.xlsm again, the shortcut no longer works at all. You would need to run your CreateShortcut macro again.
You're probably better of assigning a shortcut to that macro using Macro options:
Take the Private bit off the macro's first line, then if you're doing it programmatically create another macro:
Code:
Sub CreateShortcut()
   Application.MacroOptions Macro:="Sheet1.CommandButton1_Click", Description:="", ShortcutKey:="C"
End Sub
and run it.
As long as you save the workbook after you've run this once, you need never run it again, so you can delete it.
It's probably easier to assign this shortcut manually, but I don't know whether your spreadsheet assignment includes you having to write the code for that shortcut assignment. Anyway, you've got it now.
 

fable

New Member
No it's not, it's in Sheet1's code sheet module.
which means the command should be:
Application.OnKey "^C", "Sheet1.CommandButton1_Click"

You didn't answer this:

Using OnKey has its problems:
If you close Excel down altogether, then open MSRA2.xlsm again, the shortcut no longer works at all. You would need to run your CreateShortcut macro again.
You're probably better of assigning a shortcut to that macro using Macro options:
Take the Private bit off the macro's first line, then if you're doing it programmatically create another macro:
Code:
Sub CreateShortcut()
   Application.MacroOptions Macro:="Sheet1.CommandButton1_Click", Description:="", ShortcutKey:="C"
End Sub
and run it.
As long as you save the workbook after you've run this once, you need never run it again, so you can delete it.
It's probably easier to assign this shortcut manually, but I don't know whether your spreadsheet assignment includes you having to write the code for that shortcut assignment. Anyway, you've got it now.
Works like a charm.
Thank you very much for your help.
Sorry I didn't answer all your questions, I was in a bit of a rush last night.
I saved it once after using it so it appears to be permanent.
Thanks again!
fable
 
Top