link key from keyboard with button to run the code instead of click on the button

[MKLAQ]

Hi guys
I have project contains many buttons inside the sheet whether active X or not and userform contains commandbuttons .
so I want to use the key from keyboard to running the code instead of click the button .
example :
button 1 (inside sheet) = F1 from keyboard
commandbutton1 active X (inside sheet)=C (letter from keyboard)
commandbutton1 (onuserform)= F3 from Keyboard and so on for each button .
also posted here
link key from keyboard with button to run the code instead of click on the button
thanks in advanced.

 

[Jaafar Tribak]

Thanks Jaafar
when I use CTRL+E
doesn't run the macro just show EXCEL message !

Don't use capital E. Use small e instead .

Also, restoring CTRL+e should be as follows: (omitting the second parameter) otherwise, it will just disable the keys combination.

Private Sub Workbook_Deactivate()
    Application.OnKey "^e"
End Sub

 

[MKLAQ]

Don't use capital E. Use small e instead .

even that doesn't work !

 

[Jaafar Tribak]

even that doesn't work !

What do you mean it doesn't work? Does it show the excel flash fill warning dialog when pressing ^e?
Can you not use some other key?

 

[MKLAQ]

when press CTRL+e will show message to inform me macro1 is not available or disabled!

Can you not use some other key?

I change to a , h . nothing changes
may you tell me if work for you?
if work for you what my bad?

 

[Jaafar Tribak]

when press CTRL+e will show message to inform me macro1 is not available or disabled!

I change to a , h . nothing changes
may you tell me if work for you?
if work for you what my bad?

Make sure you put the entire code below in the ThisWorkbook Module

Private Sub Workbook_Activate()
    Application.OnKey "^e", Me.CodeName & ".macro1"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "^e", ""
End Sub

Sub macro1()
    MsgBox "hi"
End Sub

The code will be functional after opening the workbook or after activating it .

After opening the workbook, hold down the CTRL key and press the e key and see what happens.

 

[MKLAQ]

it works pefectly .
just question, is not possible to make the macro in Standard Module instead of ThisWorkbook Module?
because I have many macros in Standard Module and I would select letter for each macro alone.
if I change e letter to ENTER key , how should be?

 

[Jaafar Tribak]

it works pefectly .
just question, is not possible to make the macro in Standard Module instead of ThisWorkbook Module?
if I change e letter to ENTER key , how should be?

Yes.

Place this in the ThisWorkbook Module:

Private Sub Workbook_Activate()
    Call HookKeys(True)
End Sub

Private Sub Workbook_Deactivate()
    Call Hookkeys(False)
End Sub

And place this in a Standard Module:

Sub HookKeys(ByVal bHook As Boolean)
    With Application
        If bHook Then
            .OnKey "^~", "macro1"
            .OnKey "^{ENTER}", "macro1"
        Else
            .OnKey "^~"
            .OnKey "^{ENTER}"
        End If
    End With
End Sub

Sub macro1()
    MsgBox "hi"
End Sub

Now, every time the workbook is activated or open, the CTRL + ENTER keys will be functional and execute macro1.
When the workbook is deactivated or closed, the keys combinations above will be restored to their default.

 

[MKLAQ]

excellent !
sorry I ask too much !
if I would use F7 or F10
I try with this

Sub HookkKeys(ByVal bHook As Boolean)
    With Application
        If bHook Then
            .OnKey "^F7", "macro1"
            .OnKey "^{F7}", "macro1"
        Else
            .OnKey "^F7"
            .OnKey "^{F7}"
        End If
    End With
End Sub

but seem onkey is wrong !

 

[Jaafar Tribak]

excellent !
sorry I ask too much !
if I would use F7 or F10
I try with this

Sub HookkKeys(ByVal bHook As Boolean)
    With Application
        If bHook Then
            .OnKey "^F7", "macro1"
            .OnKey "^{F7}", "macro1"
        Else
            .OnKey "^F7"
            .OnKey "^{F7}"
        End If
    End With
End Sub

but seem onkey is wrong !

Sub HookkKeys(ByVal bHook As Boolean)
    With Application
        If bHook Then
            .OnKey "^{F7}", "macro1"
        Else
            .OnKey "^{F7}"
        End If
    End With
End Sub

Please, take a look at the ms documenttion:

Application.OnKey method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[MKLAQ]

much appreciated for your time and assistance .
thank you for everything.