How To Assign Keyboard Shortcuts To Macros In Excel
Вставка
- Опубліковано 24 лип 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video I explain 2 ways to create keyboard shortcuts to run macros in Excel. Assigning shortcut keys to macros in your Personal Macro Workbook allows you to run the macros on any open file.
Download the sample Excel file here: www.excelcampus.com/vba/keybo...
The first way is using the Macro Options window. This can be accessed from Excel, and is easy to setup.
The second way is using VBA code and the Application.OnKey method. This method requires a bit more setup work, but does have some distinct advantages.
Which method is better? Watch the video to find out. :-)
Please leave a comment below with any questions or suggestions.
Related videos & posts:
How to Create Your Personal Macro Workbook: • How to Create a Person...
How to Run Event Macros based on User Actions:
• How To Create Vba Code...
Add Macro Buttons to the Ribbon Toolbar: • How to Add Macro Butto...
How to Enable the Developer Tab:
• How to Enable the Deve...
Best keyboards for Excel Shortcuts: www.excelcampus.com/keyboard-...
18 Excel Shortcuts for 2018:
www.excelcampus.com/vba/excel...
00:00 Introduction
00:40 Macro Options Window
02:51 Macro Options Window Pros and Cons
04:45 Onkey Method
09:55 Pros and Cons
12:51 Conclusion
The only instructor who clearly says how to include the "Shift" in Shortcut key - thank you!!
Superbly structured tutorial, with great logical flow of the ideas.I understood immediately and used it for creating my shortcut keys.
Such a helpful tutorial, thank you!
I was impressed that you added the extra bits for housekeeping the shortcuts using Events. I've learnt even more about VBA than expected in a very practical and simple manner!
😟😟😟😟😟
Hi Jon.. excellent video and summary of the two methods of creating macro shortcut keys. I've always been frustrated by the limited choices in the options window. OnKey is the way to go. Thanks for sharing your tips. Thumbs up!
This is an excellent video, Jon. It's exactly what I needed today and you explained the concepts very clearly. This is the first video I have seen from you. I am now a subscriber.
Imressive tutorial on shortcut for your favorite macros 👍🏻
Thanks for taking the time...
Thank-you for the video. I prefer to use the workbook activate and deactivate events vs. open and before close.
Thanks man, I was in immense need of this onkey method, I'm in a data entry temparory job and have already connected ctrl + shift shortcuts of all keys except L & F.
Now gonna use onkey method and will make more use of macros
how to apply f1or home key for excel userform command button
Really good video. Super accurate and thorough. Also, you've got a great voice and the narration was very easy to listen to and understand.
Excellent explanation, and I like how you use the workbook events to toggle macro shortcuts on and off.
Thank you Clay! :-)
Thank you! This will really give a new level for Excel applications :)
Thank you John, great informative video
Love the automated event thing. I taught myself how to write macros slowly and eventually wrote hundreds of lines and dozens of procedures with maybe 15 shortcuts.... But I'd always have to run the code at the start of the day to enable the shortcuts 😭 wish I would've known about the automated events. Great vid, I also prefer the code way to assign shortcuts
Great vid.
Excellent job Jon 👍👍👍
✅ Overall, great video John - thanks for taking the time to put this together
➖ The only thing I would say is that for me it was a little on the long side (found myself skipping ahead several times)... this is really a minor point though, like I say overall this is a great video - thanks again! 👍🏼
As always, thank you Sir 😁
Thanks a lot ❤❤
Nice Video. Thank you
Very useful. Very helpful. Thanks.
Thank you Don! :-)
Hi
Thank you for sharing useful stuff.
It works fine with any workbook but does not work on custom ribbon control I have designed using xml ui editor. Shows an error "Argument not optional" since I have assigned the same macro control to a button on the ribbon.
Need your thoughts please.
Thanks
Perfectly explained.
Glad you liked it 😀
Hooorah! Thanks for sharing.
You're welcome! 😀
Hey john, great tutorial, was very helpful. I do have a question though.
Is there a way to activate the macro without actually going into the vba window and pressing play (like you did in 6:43 )?
Hello ... Very well explained. Thank you :) ... However, I have a question:
1. How to set up a hotkey (while developing a spreadsheet on a Windows machine) to work on Excel for Mac?
2. If the method (or content of the 1st parameter for the OnKey method) is different for Mac then is there a way to detect on which machine the spreadsheet is currently opened?
Sir I need your help with a question I have for so long that's driving me crazy.
I recorded simple macro to highlight any cell yellow using a shortcut ctrl+Q which works absolutely fine whenever Im on any cell and hit ctrl+Q, I get my cell highlighted in yellow.
But I want to non highlight the cell that's alright yellow highlighted with the same ctrl+Q shortcut. Essentially Ctrl+Q to highlight and the same Ctrl+Q to unhighlight the yellow cell (if it's alright highlighted yellow). But am not able to do it and not sure how it is done.
I believe in my previous organisation where I used to work for, they had an add-in in the ribbon named 'Audit tools' wherein this macro was there. I used to hit ctrl+Q to yellow highlight and same ctrl+Q to remove the yellow highlight.
Can you please help?
ok thanks
nice sir
I set the shortcut keys, but the macro won't run when I hit CNTRL + SHFT + A.
I have pressed it every which way. It's a code in my personal macro.
Is there a way so if I press certain buttons, a series of steps will happen in a certain computer program. When researching, we were using gel electrophoresis and the program we used to analyze it in the computer had many steps and it would be easier to write a program. It would be amazing if we can do it so that the shortcut runs the gel, analyze it, print a copy, and email to yourself.
Hi Jon please send us a short cut for mail drafting in outlook gmail and yahoo mail
I just wanted to answer a question that wasn't asked. It's a question I thought this video might address but didn't, so I thought I would do some testing to answer it myself and share the results in case anyone had the same question. As everyone has already stated this is a very well thought out logical video.
QUESTION: Is it possible to use the same keyboard shortcut to preform 2 different operations depending on which spreadsheet is open?
ANSWER: Yes. I may want to use Control + B to do one thing on one worksheet but something different on another worksheet. I was concerned about not being able to do this. The video does mention hierarchy if both spreadsheets are open, but I like the freedom to have specific workbook specific keyboard shortcuts. The key is to make sure both workbooks are not open at the same time. This is similar to creating a named range that is re-useable per spreadsheet.
Hi, Jon! How did you change interface of your Excel? I have Office 365 and it looks different
Hi Alex, I'm on the Monthly Targeted Channel of Office 365. This used to be called Insiders Fast. This is the pre-release build and it has the new fluent ribbon. I believe it will be updated to the regular builds in the coming months. The Monthly Targeted Channel is open to anyone if you want to try out the new features early. There are possible bugs though, so I'm not sure I'd recommend it for your daily work. I hope that helps.
Here is a link to more info on the Insider program. The channel names have changed a bit since that article was written, and I believe it needs to be updated. support.office.com/en-us/article/what-is-office-insider-f4208185-b63a-4b68-9c7a-9a32d2411c16
Tq sir
How do I even get a macro for ps4?
CAVEAT:
Use the Activate/Deactivat instead if you want to override the already defined shortcuts of Excel, otherwise, the above Open/BeforeClose won’t work - I grappled with this problem for a few days before I reach the solution!
This is exactly what Im trying to do; I want to change the key command "CTRL R" to something custom but I cant figure out how to deactivate "CTRL R" first so it can then be re-assigned. Can you explain? Thanks