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

M

MKLAQ

Active Member
Joined
Jan 30, 2021
Messages
398
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Sort by date Sort by votes
MKLAQ said:
Thanks Jaafar
when I use CTRL+E
doesn't run the macro just show EXCEL message !
Click to expand...
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.
VBA Code: 
Private Sub Workbook_Deactivate()
    Application.OnKey "^e"
End Sub
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
when press CTRL+e will show message to inform me macro1 is not available or disabled!
Can you not use some other key?
Click to expand...
I change to a , h . nothing changes
may you tell me if work for you?
if work for you what my bad?
 
Upvote 0
MKLAQ said:
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?
Click to expand...
Make sure you put the entire code below in the ThisWorkbook Module

VBA Code: 
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.
 
Upvote 0
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?
 
Upvote 0
MKLAQ said:
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?
Click to expand...
Yes.

Place this in the ThisWorkbook Module:
VBA Code: 
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:
VBA Code: 
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.
 
Upvote 0
Solution
excellent !
sorry I ask too much !🙏🙏
if I would use F7 or F10
I try with this
VBA Code: 
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 !
 
Upvote 0
MKLAQ said:
excellent !
sorry I ask too much !🙏🙏
if I would use F7 or F10
I try with this
VBA Code: 
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 !
Click to expand...
VBA Code: 
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:
learn.microsoft.com

Application.OnKey method (Excel)

Office VBA reference topic
learn.microsoft.com
 
Upvote 0
much appreciated for your time and assistance .:)
thank you for everything.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
How autonumber in textbox on userform based on click three buttons
2 3
Replies
20
Views
253
Omar M
O
K
  • Solved
Link Worksheet Change Event to Button
Replies
3
Views
111
rollis13
rollis13
I
  • Question
Add button to increment textbox value by 1
2
Replies
13
Views
153
Artik
Artik
G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
318
GarnesGambit
G
D
  • Question
Tally that shifts cells daily.
Replies
3
Views
184
portews
P

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top