How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar (Part 3 of 4)
Вставка
- Опубліковано 20 бер 2016
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
Learn how to add custom macro buttons to the Excel Ribbon or Quick Access Toolbar.
This allows you to run macros from your personal macro workbook at any time, on any open workbook.
Read the article and download the Excel file here:
www.excelcampus.com/vba/person...
The Excel ribbon can be customized with new Tabs, Groups, and Buttons that will run your macros.
You will also learn how to add macro buttons to the Quick Access Toolbar (QAT). The advantage here is that you can use the Alt+number key keyboard shortcuts to press a QAT button to run your macro. This makes it really fast to run a macro on any open workbook.
Other videos in this series:
How to Create a Personal Macro Workbook in Excel and Why You Need It (Part 1 of 4):
• How to Create a Person...
Where Is The Personal Macro Workbook Stored and How to View It (Part 2 of 4):
• Where Is The Personal ...
How to Add a Yes No Pop-up Message Box to a Macro Before it Runs (Part 4 of 4):
• How to Add a Yes No Po...
Please leave a comment below with any questions, and don't forget to hit the LIKE button if you enjoyed this video. Thank you!!! - Навчання та стиль
Thanks for making this video. I am using an older version of excel (2007). I just added it to the Quick Access Toolbar. This will save me a lot of time once I make all my macros to use on all my spreadsheets. Thanks again.
Thankyou so much! I have been searching the internet for what seems like forever to work out how to do this!!
This is fantastic help for speeding things up. Thanks for taking the time to do it so clearly!
Thank you so much for sharing. Looking forward for more videos with deep learning of VBA.
This is awesome and very well done!
Thank you so much. This is a great series. You are a great teacher and an excel genius.
Very nice explanation and well presented!
Thank you so much for the free training. It helps to my job
Thank you! Great tutorial!!!
Awsome work, very useful. Thank you.
It helped me in my work thank you sir.
Excellent content. Well-organized presentation.
Thanks for your Great Video
LOVE THIS CONTENT!
Thank you Jon for the video
Very helpful. Thanks!
very useful video, my ninja. you made it very easy
Hi, its really helpful, thanks👍
good intro and useful info, thank you
Thanks very clear and concise.
Perfect Guide!
Awesome Video !
Thank you, John !!
thanks :) I like this elegant solution. :)
Thanks for the vidéo
Great video! Showed me exactly what I needed to know quickly! Thank you!
Glad it was helpful! 😀
thank you helped a lot. Knew this was there somewhere in Ms Office
Thank you sir!
Indeed a very useful tip.... thanks a lot.
🙏🙏
My pleasure 😊
Thank you..very helpful...
Thanks a lot
Very nice and smooth learning
Thank you! 😀
great video thx
Great work❤
Thank you! 😊
great sir knowledge provider
Thank you!
You're welcome! 😀
Great series.. these videos were very helpful.... much appreciated.
pro tip : if hes going too slow in the video for your liking then just bump up the playback speed to 1.75 saves a bunch of time.
Thank you
What an explanation,wOW......
Awesome! Just tell me this one thing.
If I export customization in my computer and then later import it on your computer, do I have to share my Personal Workbook with you as well, or will it take care of that automatically? How would your computer find the Macros that I have stored in my personal workbook, otherwise?
Thanks for explaining this...
Thanks for a great video. Customize QAT has the ability to assign a macro specific to the active workbook itself (e.g., see a Drop Down menu on the top right side which can be selected for your active book - it is listed under "For all documents (default)"). That way you will only assign the macros which are available in the active book in the QAT itself. There does not seem to be an ability to do the same with the custom ribbon itself. You can only use the "Main Tabs" selection which forces all your spreadsheets to have the same buttons. Is it possible to segregate the macros on a book by book basis in a user defined custom tab?
Great, thank you very much. So hard to do it through external apps. Is it somehow possible to custimize the icons using external images?
Thanks..
thank you
Thank you for the clear explanations! Is there a way to add the new buttons in the ribbon only when the user opens the workbook (or addin) containing the functionality launched by the tools? I also need to know how to remove those new buttons in the ribbon, as soon as the user closes the workbook (or addin) carrying that same functionality. Can we do this with VBA?
Finalllyyyy!!!
First of all thank you so much for the idea. When I open a new Excel file, I could see my Macro under menu but when I press the button, open my original created file. Any idea to add these keys without original file open?
Good Sir
Good info 👍🏻
Let know if we can add our own pictures in addition to the existing ones which we see if select INSERT -> PICTURES Option. Thanks
Awesome Video. Thanx... ONE QUESTION : Is there any way to create dropdowns in the ribbon ?
Hi Jon, Thank you for this wonderful explanation and sharing knowledge. It helped me in a great way, although I am facing a problem which is : when I send this excel file to other people they don't see the customization I've made. Can you please help? Thanks.
Hi Jon, thanks for explaining how to make a macro available in new workbooks. The concept of Personal workbook is very well explained.
I have a question, if we have a user function and we want this function to be available for all the new workbooks we create, how do we achieve it.
Hi Jon, thank you for the video. I have two questions. 1) Is it possible to customize only a specific template and not Excel in general? I think it is, because in my office we use a tool developed in excel that has a special tab that is showed only in that specific file or in its copies. How do I customize only a specific file? 2) Do the customized tabs have to be necessarily related to macros belonging to the Personal workbook or whatever module present in the current workbook is good? Thank you, bye, Marco.
Hi Jon... awesome video. Question, how do I delete old macros that I don't use any longer... they seem to occupy my logical short-key I use to run my macros
Hi
Thanks a lot for sharing this.
Kindly help, how we can add a tab including a group and other macros to other system, like AddIns.
good
Hey first thanks for sharing your good work and knowledge. I am facing problem in saving after customizing, when i reopen and try to run macros it gives error but when i Import save customization UI file it runs.
I have created a macro in a file.xlsm , then i have add this marco in customize ribbon (same this video) , every time I run it with the button on the ribbon, the macro works but the file.xlsm always opens....How can I avoid this?
Hi
Thank you for useful information.
I have created a custom ribbon through Xml UI Editor and want to callback a User Defined Function from a button on the ribbon control.
For instance, the way a built-in formula is inserted in an active cell.
Hope my question is clear.
Thanks
Hi, thank you for your Video, as you say you would like to help: the buttons I am trying to create in my VBA editor is about IF THEN and FOR NEXT button so I don't need to write this statements all the time, just click and write the variable, how is possible to do that. thanks.
Hello Jon, this video has resolved part of my problem, here is the second part: It's there a way to keep this buttons while using the file in another computer?, I have created the buttons but they disappear when I open the file in another computer, Thanks
good stuff... do you have a video on creating an add-in? I want to make. my macros from this one module to others in my Dept... but I don't want to make them have to create a workbook called personal. xlsb, and save it in the xlstrart directory
Dear sir greetings
I have to know that if we exporting UI file to another computer. Is Macros also copied to it?.
Thanks in advance
Thanks for video. Can you advise is there any way to expand collection of icons (symbols) which can be assigned to macro? A standard one here 1:59
Thanks for the help. After doing this I noticed that the customized ribbion is showing in every open workbook. Is there any way to cusomize ribbion for one particular workbook ?
Hi Jon, thanks for this series! I found so many videos on macros but not many about orienting the user with regard to navigation. totally clear now.
With regard to the export import feature, will it carry my actual subroutine with the ribbon export or only the superficial buttons? I've recorded a macro that I'd like to make accessible on another pc. I'm guessing that I will have to save my PERSONAL doc to a jump drive to transfer the macro to the alt PC... but importing the buttons and actually attaching it to the macro is unclear. If you have opportunity, please clarify. Thanks again!!
Thank you Sherinne! You are correct. That is a great question and I should have explained it better. The CustomUI file that you export does not contain the macros or files that the macros are in. It just contains the buttons. So you will also need to transfer the Personal Macro Workbook to the other computer and put it in the XLStart folder. In video 2 of this sereis I explain where the file is stored. Here is the link. ua-cam.com/video/6cynP3OkIT0/v-deo.html
Excel Campus - Jon you're the best, thank you much!
I saved off the customization you said to import it onto a new pc can be done. So i have that file it created do i need to go through the customize ribbon to get to that import button and then just click that?
How about when you open a file the workbook creates a temporary add in and disable temporarily some of the usual add ins
Thanks. However while my buttons do show up in the QAT but they won't run the macros. The macros won't run from the button on the ribbon either. Changing settings in "Trust Center" does not help. The only way I can get my macros to run is by hunting them down in the Developer tab.
Do the macros need to be "public" to be available to assign? Can they be inside of a userform in the Personal.xlsb?
Hi Jon, do you perhaps know how to add a description on hover? Other ribbon items have descriptions when you hover over them.
Thanks M.
Hi Morne, I don't know of a way to add the screentip (description) to this type of ribbon customization. You can modify the screentip and supertip when you create an add-in with CustomUI XML code for the ribbon. The CustomUI code gives you a lot more option for customizing the ribbon to add your own button images, screentips, and various control types (buttons, drop-downs, checkboxes, etc.). I hope that helps.
hey jon, i have a question here that i have created the module in a excel and the same was worked fine. However once i closed the excel and reopened the same i can't see that module thus i'm unable to use from 2nd time onward, how to use/store permanently
hi :) excellent...I just added 3 sheets, but when I click on Marco it adds me 6 sheets :)
I have a question. How can I add the "Design" tab in the ribbon in excel 2013?
I was able to add developer but i dont seem to have a use for that yet.
That being said i have one more question.
How can i get a worksheet/template to run like a combined one instead of seeing a bunch of tabs at the bottom of my excel.
The template was bought and is used for various things which after it is all added up in the first few sheets of the template it shows the outcome of the total added.
On my Win 10 YogaBook it works fine but on my laptop its not and its the same template.
Please help.
You say that if “I get a new PC I can import my Macros “? Therefore, when I transfer my files to my new PC does not transfer my macros ? Sorry for the confusion. Hope you can reply. 🙏
Is there any way to keep our own icons instead of picking up only the available icons ?
Hi John,
I am trying to create this tool bar and I've been following your videos. First, when I pasted the file location, nothing was saved at the XLSTART file. Second, when I created the tab and tried to transfer the macro I had created, it was not an option. I feel like I'm missing something.
--Dulce
Thanks. Great video. The custom ribbon /toolbar creates the icons for me as described but the macro won't run from there due to a security setting I cannot find. (The macro runs fine from the standard macro list). Pulling my hair out 😂
Once you add a macro to the quick acces tool bar and ribbon, do you have to update the tool bar and ribbon if you make changes to the marco.
What should I do if I want other people who open this workbook to have the same tab and use it without having to enter to the customize ribbon right click option? Thanks
Hi Catu,
In this case you can customize the Excel ribbon with XML code. I explain how to do this step-by-step in my VBA Pro Course. excelcampus.com/courses. I hope that helps.
In my office you do not have the customize ribbon option, how do I? My office is 2016, I really wanted it was that the menu I created with the Ribbon appeared in all excel
How do I make a button for Mac? After following the steps, when I get to rename I do not have the option to choose a symbol and it shows on the excel as an invisible button that can still be clicked.
How do I attach it to the word file? I want the Macro buttons to be available to anyone who uses the excel file?
Hi. Hello from Argentina.
If I customize my Excel Ribbon whit personals macros and I want to export it to another computer: ¿ Is enoughf the Import/ export Excel Customizations? ¿Will the macros stores in the PERSONAL.XLSB be exported?
Thanks!
Sir,
I have done this in my system and if I wants to convert this as addin ?
Like group of multiple VBA programs which are already in ribbon to convert as addin and share it with others directly is it possible???
How can I use my regularly used file to Custom Ribbon to use when I click on it, without using vba or macro
Won't it be available only for you/on your computer? What if I want to send this file to someone else via email? Will this person also have access to those buttons?
If i share the file will the tab will appear on another device?
Hi JonI have made my macro and added it to the ribbon, but when I press it, nothing happens but if I use the shortcut keys it works. I've tried removing the shortcut keys, but it doesn't seem to make a difference.Hope you can help and thank you for the video.
Hey Jon, I worked it out. The Personal makro folder was disabled :-(
Hi Jon,
I wonder if you could help me solve this problem, I watched your video clip “ How to Create a Personal Macro Workbook” in Excel 2011 for Mac.
I started to record the “Add 3 Sheets Macro” and got interrupted so I closed Excel and commence again and I got the following Message,
Screen Shot 2016-06-25 at 6.51.27 AM.png
Screen Shot 2016-06-25 at 6.51.49 AM.png
So I closed excel again and reopen Excel, I get the following Message.
Screen Shot 2016-06-25 at 6.43.07 AM.png
Jon, I don’t know what I have done your help would be appreciated.
Cheers,
Don
hello,
any idea to add a "save as" button to save just a specific pivot table (I want the user to choose which pivot table need to save)
I'm not sure I fully understand, but I believe this could be done with a macro. You could copy the pivot table to a new workbook. You would just need to be conscious of if you want it to link to the source data in the original workbook, or be the report only. Any actions you take to set this up manually could be automated with VBA.
We do have a community forum inside our Elevate Excel Training Program where members can ask more detailed questions like this.
www.excelcampus.com/elevate
I hope that helps. Thanks again and have a nice weekend! 🙂
But don't you lose all those configs once the file is open on other computer?
How do you do this on a Mac? We dont have a Customize the Ribbon part that allows us to make custom buttons ON THE RIBBON.
How do I do the same in excel 2016 for Mac, pls?
How to activate "My Macros" with a macro instead of using the Config options?
will it apply on any excel workbook or file as long as its the same PC... or willit be just for the specific workbook where you customized the ribbon/tab?
Hi Jenna,
Great question! The customized ribbon will appear on all open workbooks on the PC you customize it on. This type of Ribbon customization is specific to the Excel application, not the workbook. I hope that helps. Thanks!
You can create your own excel addin and place the code within that. That will then work on all workbooks regardless.
Thank you👍😀
When I am sending this file the customized ribbon is disappear. How to fix this problem.
When we copy files excel to other laptop, those other laptop have code in Ribbon as this video?
Great question Seang! No, the ribbon does not travel with the workbook for this type of setup. You will have to setup the same ribbon buttons on your laptop as well. You can click the Import/Export button on the Customize Ribbon tab to export the customizations, then import them into Excel on your laptop.
If you want the buttons to travel with the workbook then you can create an Excel Add-in (xlam) or Macro Enabled file (xlsm) and use Ribbon XML to create the buttons. This requires creating XML code in the file to create the buttons.
I hope that helps. Thanks again and have a nice day! 🙂
After Creating macro and tool belt, when running macro, It shows run time error 9. Kindly advise the solution.