How to move custom VBA/ribbons

[missingsc]

I posted a similar thread some time ago and unfortunately didn't understand the answer given at the time. Hoping to start a fresh discussion on how to do this...

I have a custom spreadsheet I've built containing VBA subs/functions and forms. To interact with that functionality, I've also modified the Ribbon for new buttons to run those functions as needed. I want to be able to reuse this spreadsheet and give it to others. The problem I've run into is multi-fold:

• The customized Ribbon isn't sent with the spreadsheet by default. I need to export the customizations and ask the user to load them. Can that be somehow done automatically?
• The custom VBA and forms are not sent with the spreadsheet. The only way I've found to send them is with an Add-In, which the user then has to load manually.
• When the Ribbon customizations are loaded, the buttons do not work. They error out saying the sub/function cannot be found because a file path/url cannot be found. I have to delete the button, then recreate it and link it to the sub/function/macro by hand.

Obviously the question is how can this be done so the user doesn't have to do anything? They just get the spreadsheet and it's ready to go? Failing that, how close can we get to making it automatic for them? At worst, I'd like to be able to send them the Add-In and Ribbon Customization file with instructions on how to load it, but as said that doesn't work either.

Looking forward to all the answers you have as I'm stuck being able to share this with anyone. Thank you in advance!

 

[Micron]

What about using the Quick Access Toolbar with icons to run code in the workbook? I cannot recall if I've ever sent anyone a wb and had to worry about whether or not those will be visible to the recipient. As long as the code is included (it should be if the sent file is .xlsm) and the recipient puts the wb in a Trusted Location, things should work?

Customized Ribbon can be quite difficult to get working properly as I've discovered with Access, even though the xml code goes with the database. If Excel ribbon code needs to be an add-in, that seems rather clunky to me.

Another way of executing code could be via a userform.

 

[RoryA]

The custom VBA and forms are not sent with the spreadsheet

If that is the case, then they are not part of the workbook, they are part of another file (or you saved the workbook as a macro-free format before sending it).

You can customise the ribbon for a workbook (or add-in) by modifying its CustomUI XML part. Thankfully, Jan Karel has taken over Ron de Bruin's Windows tips pages, so you can find a wealth of information here: Ribbon and Quick Access Toolbar pages

 

[missingsc]

Customized Ribbon can be quite difficult to get working properly as I've discovered with Access, even though the xml code goes with the database. If Excel ribbon code needs to be an add-in, that seems rather clunky to me.

I'm open to suggestions on how to do this as well. The buttons either run a function, such as "Refresh Dashboard", or calls a user form. How have you accomplished this in your experience?

If that is the case, then they are not part of the workbook, they are part of another file (or you saved the workbook as a macro-free format before sending it).

You can customise the ribbon for a workbook (or add-in) by modifying its CustomUI XML part. Thankfully, Jan Karel has taken over Ron de Bruin's Windows tips pages, so you can find a wealth of information here: Ribbon and Quick Access Toolbar pages

It is a .xlsm file, macros enabled. All my code is either in Forms or Modules. When I go to the VBAProject tree, they are show as part of the .xlsm file. Maybe I'm misunderstanding what you are saying though?

 

[RoryA]

If they are part of the .xlsm file then they will travel with the workbook, unless you save it as an .xlsx file before sending, or the recipient has something removing the code (e.g. they don't have VBA installed).

 

[missingsc]

If they are part of the .xlsm file then they will travel with the workbook, unless you save it as an .xlsx file before sending, or the recipient has something removing the code (e.g. they don't have VBA installed).

OK, I am wrong, I apologize. I must have gotten confused when trying to work through this. Thank you for helping.

The ultimate question remains though: even with the code in place, and even if I can import a custom Ribbon, the buttons do not work. They error saying I do not have access to some long live.com URL. I have to recreate each button manually. Any thoughts?

 

[RoryA]

You need to review the link I sent earlier.
Making a custom tab/buttons in the XML of the workbook will travel with the workbook and work for all users.

 

[missingsc]

You need to review the link I sent earlier.
Making a custom tab/buttons in the XML of the workbook will travel with the workbook and work for all users.

Oops, my apologies. In responding I missed the link. I will give it a read and try it out. Thank you for sending it!

 

[RoryA]

No problem. It's easier than it looks at first glance, honest! (as long as you can get and use the CustomUI editor. it's a pain if you have to do it manually)

 

[missingsc]

Coming back here to say thank you RoryA! I followed the page and tinkered a bit and was able to get it working, and now I can send it to others with the code and the ribbon configurations. Really appreciate your help!!!