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.
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?
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?
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'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! 🙂
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
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 ?
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
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???
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
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.
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.
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.
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. 🙏
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?
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?
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
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?
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!
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...
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.
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
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! 🙂
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?
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.
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.
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.
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 😂
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
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
It seems that there are a lot of information that doesn't apply to Mac users. It would be nice to know if the training would apple to Mac user. Or maybe cover the lessons for Mac user.
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
hi Jon. I have found your videos very helpful but am now stuck. I am trying to record a macro to perform this task trumpexcel.com/highlight-active-row-column-excel/ but when I go to run the macros in another worksheet it isn't doing what I can. The highlights are there but when I go to click on another cell it doesn't move. I am trying to automate this so I can have a quick button for my boss. There error I am making seems to be with the VBA code placement I think.
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.
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, 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. ruclips.net/video/6cynP3OkIT0/видео.html
+crewcutter2030 Great question! The ribbon customizations will NOT appear on other computers. These customizations are made to your computer only. If you want the buttons to appear on other people's computers when they open a file, then you will have to customize the ribbon XML in the file. You can do this for a macro enabled workbook (.xlsm) or add-in file (.xlam). There are a few free tools that make this easy, including the Ribbon Editor by Andy Pope. www.andypope.info/vba/ribboneditor.htm I hope that helps. Let me know if you have questions. 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.
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.
Thank you so much for sharing. Looking forward for more videos with deep learning of VBA.
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!
LOVE THIS CONTENT!
Thank you so much for the free training. It helps to my job
very useful video, my ninja. you made it very easy
Very nice and smooth learning
Thank you! 😀
Perfect Guide!
Great video! Showed me exactly what I needed to know quickly! Thank you!
Glad it was helpful! 😀
Indeed a very useful tip.... thanks a lot.
🙏🙏
My pleasure 😊
Thank you, John !!
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
Hi, its really helpful, thanks👍
Awesome Video !
good intro and useful info, thank you
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?
Thank you so much. This is a great series. You are a great teacher and an excel genius.
How do I attach it to the word file? I want the Macro buttons to be available to anyone who uses the excel file?
thanks :) I like this elegant solution. :)
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?
Thank you Jon for the video
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.
Thank you! Great tutorial!!!
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! 🙂
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.
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
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 ?
Great work❤
Thank you! 😊
After Creating macro and tool belt, when running macro, It shows run time error 9. Kindly advise the solution.
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
Thank you sir!
Very nice explanation and well presented!
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
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???
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
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.
But don't you lose all those configs once the file is open on other computer?
If i share the file will the tab will appear on another device?
Do the macros need to be "public" to be available to assign? Can they be inside of a userform in the Personal.xlsb?
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.
Thanks for your Great Video
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.
This is awesome and very well done!
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 ?
Thanks very clear and concise.
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?
thank you helped a lot. Knew this was there somewhere in Ms Office
Thank you..very helpful...
How can I use my regularly used file to Custom Ribbon to use when I click on it, without using vba or macro
Thank you!
You're welcome! 😀
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?
How about when you open a file the workbook creates a temporary add in and disable temporarily some of the usual add ins
Great, thank you very much. So hard to do it through external apps. Is it somehow possible to custimize the icons using external images?
How to make a Menu in VBA Form?
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
How to activate "My Macros" with a macro instead of using the Config options?
Does this work on a MacBook?
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.
In WINDOWS 7 I am not able to find the option "Customize Ribbon"
Thanks for the vidéo
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?
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!
Thanks a lot
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...
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.
Excellent content. Well-organized presentation.
It helped me in my work thank you sir.
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
Why I can't rename my customized tab?
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! 🙂
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?
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.
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 :) excellent...I just added 3 sheets, but when I click on Marco it adds me 6 sheets :)
When I am sending this file the customized ribbon is disappear. How to fix this problem.
Awsome work, very useful. Thank you.
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.
How do I do the same in excel 2016 for Mac, pls?
great sir knowledge provider
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.
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 😂
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
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
It seems that there are a lot of information that doesn't apply to Mac users. It would be nice to know if the training would apple to Mac user. Or maybe cover the lessons for Mac user.
sir its not working,working only when i run macro
Great series.. these videos were very helpful.... much appreciated.
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
hi Jon. I have found your videos very helpful but am now stuck. I am trying to record a macro to perform this task trumpexcel.com/highlight-active-row-column-excel/ but when I go to run the macros in another worksheet it isn't doing what I can. The highlights are there but when I go to click on another cell it doesn't move. I am trying to automate this so I can have a quick button for my boss. There error I am making seems to be with the VBA code placement I think.
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.
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.
Awesome Video. Thanx... ONE QUESTION : Is there any way to create dropdowns in the ribbon ?
Thank you
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
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. ruclips.net/video/6cynP3OkIT0/видео.html
Excel Campus - Jon you're the best, thank you much!
great video thx
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.
When another computer/ excel, open your workbook, will it still have the customized ribbons on it?
+crewcutter2030 Great question! The ribbon customizations will NOT appear on other computers. These customizations are made to your computer only. If you want the buttons to appear on other people's computers when they open a file, then you will have to customize the ribbon XML in the file. You can do this for a macro enabled workbook (.xlsm) or add-in file (.xlam). There are a few free tools that make this easy, including the Ribbon Editor by Andy Pope. www.andypope.info/vba/ribboneditor.htm
I hope that helps. Let me know if you have questions. Thanks!
Excel Campus - Jon
Thanks, ill check it out on how to make it appear on other peoples excels.
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.